How to summarize a text planning grid?

[SURFER349]

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

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Something like this. The extra kicker is that there are ~100 rows and ~100 columns, so hand writing one big concatenation formula seems daunting.

Tool id	Items
Group1	A,B,C,D
Group2	C,D
Group3	C,D
Group4	C,D
Group5	D
Group6	A,B,C,D

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[DRSteele]

This formula requires function TEXTJOIN, which is part of Excel 2016 only. Properly invoke B11 and then copy down.

	A	B	C	D	E
1	Tool ID	A	B	C	D
2	Group1	x	x	x	x
3	Group2			x	x
4	Group3			x	x
5	Group4			x	x
6	Group5				x
7	Group6	x	x	x	x
8
9
10	Tool id	Items
11	Group1	A, B, C, D
12	Group2	C, D
13	Group3	C, D
14	Group4	C, D
15	Group5	D
16	Group6	A, B, C, D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Array Formulas Cell Formula B11 {=TEXTJOIN(", ",1,IF(IF(A11=$A$2:$A$7,$B$2:$E$7,"")="x",$B$1:$E$1,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>