stumped by my boss...

robd11 · Mar 13, 2019

Ive been lurking on these boards since I started working and a former boss of mine steered me to the site. you don't know it but many of you have been incredibly helpful over the last 6+ years, so thank you.

boss of mine decided to start a new monthly excel challenge and while I have one way of solving the problem, it is not the more "elegant, simplified" way he does it. it's absolutely killing me and he won't share until someone figures it out...problem is, google and this site are generally how I solve these problems and I can't find exactly what Im looking for...so here I am

its a simple data set (table below showing small example), and the idea is to write a formula in column j that will identify the 4 digit project numbers (B1-G1) that each employee spent >= 50% of their time on. Column I has the answers. Before anyone suggests it, I've already determined that I can CONCATENATE multiple IF statements, but he insists there is an easier way.

let er rip

[TABLE="width: 500"]
<tbody>[TR]
[TD]employee
[/TD]
[TD]1234
[/TD]
[TD]1235
[/TD]
[TD]1288
[/TD]
[TD]1646
[/TD]
[TD]1586
[/TD]
[TD]1975
[/TD]
[TD][/TD]
[TD]answers
[/TD]
[TD]formula
[/TD]
[/TR]
[TR]
[TD]a
[/TD]
[TD][/TD]
[TD]50%
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1235,1288
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b
[/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1235
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1646
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d
[/TD]
[TD][/TD]
[TD]70%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]30%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1235
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD]1975
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]40%
[/TD]
[TD][/TD]
[TD]60%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1288
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50%
[/TD]
[TD]50%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1646,1586
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

forgive me for not attaching the file, work computer restrictions...

Fluff · Mar 13, 2019

If your boss has Office 365, he might be using the Textjoin function.

robd11 · Mar 13, 2019

no unfortunately we live in the stone age and are using office 2010...came across that formula in my search, nifty

sandy666 · Mar 13, 2019

you can try PowerQuery add-in for 2010 then

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]employee[/td][td=bgcolor:#5B9BD5]1234[/td][td=bgcolor:#5B9BD5]1235[/td][td=bgcolor:#5B9BD5]1288[/td][td=bgcolor:#5B9BD5]1646[/td][td=bgcolor:#5B9BD5]1586[/td][td=bgcolor:#5B9BD5]1975[/td][td][/td][td=bgcolor:#70AD47]employee[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

50%

[/td][td=bgcolor:#DDEBF7]

50%

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]1235,1288[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td][/td][td]

100%

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]b[/td][td]1235[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]c[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

100%

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]1646[/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td][/td][td]

70%

[/td][td][/td][td][/td][td]

30%

[/td][td][/td][td][/td][td]d[/td][td]1235[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]e[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

100%

[/td][td][/td][td=bgcolor:#E2EFDA]e[/td][td=bgcolor:#E2EFDA]1975[/td][/tr]

[tr=bgcolor:#FFFFFF][td]f[/td][td]

40%

[/td][td][/td][td]

60%

[/td][td][/td][td][/td][td][/td][td][/td][td]f[/td][td]1288[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]g[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

50%

[/td][td=bgcolor:#DDEBF7]

50%

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]g[/td][td=bgcolor:#E2EFDA]1646,1586[/td][/tr]
[/table]

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivotOC = Table.UnpivotOtherColumns(Source, {"employee"}, "Attribute", "Value"),
    Condition = Table.AddColumn(UnPivotOC, "empl", each if [Value] >= 0.5 then [employee] else null),
    Filter = Table.SelectRows(Condition, each ([empl] <> null)),
    Group = Table.Group(Filter, {"employee"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Attribute")),
    ExtractVal = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    ExtractVal[/SIZE]

sheetspread · Mar 13, 2019

robd11 said:
I can CONCATENATE multiple IF statements, but he insists there is an easier way.

An array formula will return the desired results in separate cells. Excel version 2010 or earlier cannot concatenate a range without VBA/morefunc etc. So maybe try:

Code:

Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String)
Dim myarray As Variant
myarray = Evaluate("=IF(" & rng.Address & ">=" & rng3.Address & "," & rng2.Address & ")")
condtextjoin = Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, "")
End Function

Excel 2010

A

B

C

D

E

F

G

H

I

J

K

1

Limit

employee

1234

1235

1288

1646

1586

1975

answers

formula

2

50%

a

50%

1235,1288

3

b

100%

1235

4

c

100%

1646

5

d

70%

30%

1235

6

e

100%

1975

7

f

40%

60%

1288

8

g

50%

1646,1586

Sheet9

Cell Formulas
Range	Formula
K2	=condtextjoin(C2:H2,$C$1:$H$1,$A$2,",")

robd11 · Mar 14, 2019

awesome. so my example doesn't show this but let's say I have an employee who doesn't spend at least 50% of their time on any one project. the custom function returns "False" as it is now, how can I get it to return a blank cell? I've played around with the code with no luck but my macro knowledge is limited.

sheetspread · Mar 14, 2019

Try:

Code:

Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String)
Dim myarray As Variant
myarray = Evaluate("=IF(" & rng.Address & ">=" & rng3.Address & "," & rng2.Address & ")")
condtextjoin = Replace(Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, ""), "False", "")
End Function

Excel 2010

A

B

C

D

E

F

G

H

I

J

K

1

Limit

employee

1234

1235

1288

1646

1586

1975

answers

formula

2

50%

a

50%

1235,1288

3

b

100%

1235

4

c

100%

1646

5

d

70%

30%

1235

6

e

100%

1975

7

f

40%

60%

1288

8

g

50%

1646,1586

9

h

22%

5%

49%

Sheet1

Cell Formulas
Range	Formula
K2	=condtextjoin(C2:H2,$C$1:$H$1,$A$2,",")

Rick Rothstein · Mar 14, 2019

sheetspread said:
Try:

Code:

Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String) Dim myarray As Variant myarray = Evaluate("=IF(" & rng.Address & ">=" & rng3.Address & "," & rng2.Address & ")") condtextjoin = Replace(Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, ""), "False", "") End Function

Excel 2010
A B C D E F G H I J K
1 Limit employee 1234 1235 1288 1646 1586 1975 answers formula
2 50% a 50% 50% 1235,1288
3 b 100% 1235
4 c 100% 1646
5 d 70% 30% 1235
6 e 100% 1975
7 f 40% 60% 1288
8 g 50% 50% 1646,1586
9 h 22% 5% 49%
Sheet1
Cell Formulas
Range Formula
K2 =condtextjoin(C2:H2,$C$1:$H$1,$A$2,",")

Here is your function as a one-liner...

Code:

[table="width: 500"]
[tr]
	[td]Function CondTextJoin(Rng As Range, Rng2 As Range, Rng3 As Range, Delimiter As String) As String
  CondTextJoin = Replace(Application.Trim(Join(Evaluate("IF(" & Rng.Address & ">=" & Rng3.Address & "," & Rng2.Address & ","""")"))), " ", Delimiter)
End Function[/td]
[/tr]
[/table]

Rick Rothstein · Mar 14, 2019

robd11 said:
...and the idea is to write a formula in column j...

You were looking for a formula solution, correct? Here is an array-entered** formula that appears to work (assumes Employee names are located in Column A and that no code in Row 1 starts with 0)...

=SUBSTITUTE(MAX(B$1:G$1*(B2:G2>=0.5))&", "&LARGE(B$1:G$1*(B2:G2>=0.5),2),", 0","")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Finalfight40 · Mar 14, 2019

I got this as an array that seemed to work for me:

=INDEX($B$1:$G$1, SMALL(IF(B2:G2>=0.5, COLUMN(B2:G2)-COLUMN(A2), ""), 1)) & IFERROR(INDEX( ", " & $B$1:$G$1,SMALL(IF(B2:G2>=0.5, COLUMN(B2:G2)-COLUMN(A2), ""), 2)), "")

stumped by my boss...

robd11

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

robd11

New Member

sandy666

Banned - Rules violations

sheetspread

Well-known Member

robd11

New Member

sheetspread

Well-known Member

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Finalfight40

Active Member

Similar threads

Forum statistics

Share this page

stumped by my boss...

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

Banned - Rules violations

Well-known Member

New Member

Well-known Member

MrExcel MVP

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock