excel dynamically summarize a text matrix?

Question

So not sure quite what I'm asking here, but seeing if you could give me pointers? Let's say I've got a planning table like this below. I'm trying to think of a way to have a second table that will list each ToolID and a single cell that shows all the 'items' that have X for them, dynamically.

Tool ID A B C D

Group1 x x x x

Group2 x x

Group3 x x

Group4 x x

Group5 x

Group6 x x x x

So here's what the final output would look like

Tool id Items

Group1 A,B,C,D

Group2 A,B

Group3 A,B

Group4 A,B

Group5 A

Group6 A,B,C,D

I'm trying to think of a way to do this dynamically, rather than spending time writing a long concatenate or vlookup for each column, because the matrix is ~100 rows and ~100 columns, so hand writing one big concatenation formula seems daunting.

Answer 1

If your first table was on sheet1 and second on sheet2 B2 on sheet2 would be

=If(vlookup($A2,sheet1!'$A$2:$E$7,match(B$1,sheet1!'$A$1:$E$5,0),0)="x",B$1,"")

If your x was upper case change it above to "X"

Copy and paste this in every cell you want a letter on you table

Answer 2

You can use TEXTJOIN if you have Excel 2016:

{=TEXTJOIN(",",1,IF(B2:E2="x",$B$1:$E$1,""))}

To activate this formula use: Ctrl+Shift+Enter instead of just Enter.