Concatenate if

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
294
Office Version
  1. 365
Platform
  1. Windows
I have a table with a long list of items, followed by multiple columns of potential "attributes" for that list. The attributes are ticked either on or off.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Organic[/TD]
[TD]Juicy[/TD]
[TD]Delicious[/TD]
[TD]Deadly[/TD]
[TD]Seeds[/TD]
[TD]Carbon-Based[/TD]
[TD]Carbon Steel[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Muskrats[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Muskets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to be able to make a list that concatenates JUST the ticked attributes



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Apples
[/TD]
[TD][/TD]
[TD]Organic, Juicy, Delicious, Seeds, Carbon-Based
[/TD]
[/TR]
[TR]
[TD]Oranges
[/TD]
[TD][/TD]
[TD]Organic, Juicy, Delicious, Carbon-Based
[/TD]
[/TR]
[TR]
[TD]Muskrats
[/TD]
[TD][/TD]
[TD]Organic, Delicious, Deadly, Carbon-Based
[/TD]
[/TR]
[TR]
[TD]Muskets
[/TD]
[TD][/TD]
[TD]Deadly, Carbon Steel
[/TD]
[/TR]
</tbody>[/TABLE]


So I built a second table, essentially:
=IF (ISBLANK (B2),"",", B$1)

Which gives me

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Organic[/TD]
[TD]Juicy[/TD]
[TD]Delicious[/TD]
[TD][/TD]
[TD]Seeds[/TD]
[TD]Carbon-Based[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Organic[/TD]
[TD]Juicy[/TD]
[TD]Delicious[/TD]
[TD][/TD]
[TD][/TD]
[TD]Carbon-Based[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Organic[/TD]
[TD][/TD]
[TD]Delicious[/TD]
[TD]Deadly[/TD]
[TD][/TD]
[TD]Carbon-Based[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deadly[/TD]
[TD][/TD]
[TD][/TD]
[TD]Carbon Steel[/TD]
[/TR]
</tbody>[/TABLE]


I know I can copy that second table into word and manipulate it into the desired list, but is there a clever way (or array) to build that same list in a cell?

(The list is 28 attributes, so that's a lot of nested IFs!)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
If you have Office 365, have a look at TextJoin, otherwise how about
Code:
Function rjbinney(Rng As Range) As String
   Dim Cl As Range
   For Each Cl In Rng
      If Cl = "x" Then
         If rjbinney = "" Then rjbinney = Cells(1, Cl.Column) Else rjbinney = rjbinney & ", " & Cells(1, Cl.Column)
      End If
   Next Cl
End Function
Used =rjbinney(B2:H2)
It assumes that you headers are in row 1, but that can be changed if needed.
 
Upvote 0
Will look into TextJoin.

I'm on a flight, and Gogo and OneDrive just a$^-f#*^ed each other, so I need to do the last 60 minutes' worth of work over again.

Thanks!
 
Upvote 0
Try this
Enter array formula in I2 and copy down
Code:
[B]=TEXTJOIN(", ",1,IF(B2:H2="x",B$1:H$1,""))[/B]
**Must be entered with Ctrl+Shift+Enter key combination.

Unknown
ABCDEFGHI
1OrganicJuicyDeliciousDeadlySeedsCarbon-BasedCarbon SteelConcatenated
2ApplesxxxxxOrganic, Juicy, Delicious, Seeds, Carbon-Based
3OrangesxxxxOrganic, Juicy, Delicious, Carbon-Based
4MuskratsxxxxOrganic, Delicious, Deadly, Carbon-Based
5MusketsxxDeadly, Carbon Steel
Sheet2
 
Last edited:
Upvote 0
Or you can use this non-array version
Code:
[B]=TEXTJOIN(", ",1,INDEX(REPT(B$1:H$1,B2:H2="x"),0))[/B]
 
Upvote 0
I was originally going to give Fluff 10 out of 10 for introducing me to TextJoin, coz that worked like a charm - did exactly what I asked.

But.

But.

But that formula, AlKey, was the bee's knees. Did exactly what I needed.

Thirteen out of ten!

(A "Huzzah" might even be in order)

Thank you,
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top