# stumped by my boss...

#### robd11

##### New Member
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

 employee 1234 1235 1288 1646 1586 1975 answers formula a 50% 50% 1235,1288 b 100% 1235 c 100% 1646 d 70% 30% 1235 e 100% 1975 f 40% 60% 1288 g 50% 50% 1646,1586

<tbody>
</tbody>

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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Fluff

##### MrExcel MVP, Moderator
If your boss has Office 365, he might be using the Textjoin function.

#### robd11

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

#### sandy666

##### Banned - Rules violations
you can try PowerQuery add-in for 2010 then

 employee 1234 1235 1288 1646 1586 1975 employee Custom a 50%​ 50%​ a 1235,1288 b 100%​ b 1235 c 100%​ c 1646 d 70%​ 30%​ d 1235 e 100%​ e 1975 f 40%​ 60%​ f 1288 g 50%​ 50%​ g 1646,1586

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]``````

##### Well-known Member
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
condtextjoin = Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, "")
End Function``````

Excel 2010
ABCDEFGHIJK
250%a50%50%1235,1288
3b100%1235
4c100%1646
5d70%30%1235
6e100%1975
7f40%60%1288
8g50%50%1646,1586
Sheet9
Cell Formulas
RangeFormula
K2=condtextjoin(C2:H2,\$C\$1:\$H\$1,\$A\$2,",")

#### robd11

##### New Member
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.

##### Well-known Member
Try:

Code:
``````Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String)
Dim myarray As Variant
condtextjoin = Replace(Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, ""), "False", "")
End Function``````

Excel 2010
ABCDEFGHIJK
250%a50%50%1235,1288
3b100%1235
4c100%1646
5d70%30%1235
6e100%1975
7f40%60%1288
8g50%50%1646,1586
9h22%5%49%
Sheet1
Cell Formulas
RangeFormula
K2=condtextjoin(C2:H2,\$C\$1:\$H\$1,\$A\$2,",")

#### Rick Rothstein

##### MrExcel MVP
Try:

Code:
``````Function condtextjoin(rng As Range, rng2 As Range, rng3 As Range, delimiter As String)
Dim myarray As Variant
condtextjoin = Replace(Replace(Replace(Join(myarray, delimiter), delimiter & "False", ""), "False" & delimiter, ""), "False", "")
End Function``````

Excel 2010
ABCDEFGHIJK
250%a50%50%1235,1288
3b100%1235
4c100%1646
5d70%30%1235
6e100%1975
7f40%60%1288
8g50%50%1646,1586
9h22%5%49%
Sheet1
Cell Formulas
RangeFormula
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

##### MrExcel MVP
...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

Last edited:

#### Finalfight40

##### Active Member
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)), "")

Replies
1
Views
349
Replies
2
Views
298
Replies
11
Views
1K
Replies
0
Views
348
Replies
2
Views
431

1,195,623
Messages
6,010,749
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back