rjbinney

Active Member
Joined
Dec 20, 2010
Messages
256
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.
ABCDEFGH
1OrganicJuicyDeliciousDeadlySeedsCarbon-BasedCarbon Steel
2Applesxxxxx
3Orangesxxxx
4Muskratsxxxx
5Musketsxx

<tbody>
</tbody>


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



Apples
Organic, Juicy, Delicious, Seeds, Carbon-Based
Oranges
Organic, Juicy, Delicious, Carbon-Based
Muskrats
Organic, Delicious, Deadly, Carbon-Based
Muskets
Deadly, Carbon Steel

<tbody>
</tbody>


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

Which gives me

JKLMNOP
1OrganicJuicyDeliciousSeedsCarbon-Based
2OrganicJuicyDeliciousCarbon-Based
3OrganicDeliciousDeadlyCarbon-Based
4DeadlyCarbon Steel

<tbody>
</tbody>


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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
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.
 

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
256
Office Version
  1. 365
Platform
  1. Windows
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!
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
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.
<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Organic</td><td style=";">Juicy</td><td style=";">Delicious</td><td style=";">Deadly</td><td style=";">Seeds</td><td style=";">Carbon-Based</td><td style=";">Carbon Steel</td><td style="font-weight: bold;;">Concatenated</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Apples</td><td style=";">x</td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style="color: #FF0000;;">Organic, Juicy, Delicious, Seeds, Carbon-Based</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Oranges</td><td style=";">x</td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="color: #FF0000;;">Organic, Juicy, Delicious, Carbon-Based</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Muskrats</td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="color: #FF0000;;">Organic, Delicious, Deadly, Carbon-Based</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Muskets</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</td><td style="color: #FF0000;;">Deadly, Carbon Steel</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Sheet2</p><br /><br />
 
Last edited:

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
Or you can use this non-array version
Code:
[B]=TEXTJOIN(", ",1,INDEX(REPT(B$1:H$1,B2:H2="x"),0))[/B]
 

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
256
Office Version
  1. 365
Platform
  1. Windows
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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
Top