rjbinney

Active Member
Joined
Dec 20, 2010
Messages
264
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,706
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
264
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.

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:

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
264
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,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,969
Messages
5,856,590
Members
431,823
Latest member
irtezazaz

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