I need to invert a table

[ffinlay]

First, Thank you for this forum. I've followed it for a few years and everyone here has been very helpful. This is the first time I've not been able to find a solution to a problem.

I need to "invert" a table. I'd thought about doing it with some 2-d lookups, but most on here are numeric based and only work with a single instance within a table. Mine is a text only table, and too big to do by hand.

Say I have a table like below:

[TABLE="width: 397"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]a[/TD]
[TD]apples[/TD]
[TD]cranberries[/TD]
[TD]pears[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]pears[/TD]
[TD]apples[/TD]
[TD]oranges[/TD]
[TD]lemons[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]cranberries[/TD]
[TD]oranges[/TD]
[TD]lemons[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]lemons[/TD]
[TD]pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]apples[/TD]
[TD]lemons[/TD]
[TD]pears[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]nuts[/TD]
[TD]apples[/TD]
[TD]oranges[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I need is the column 1 value beside each of the item types. If there are more than one, I need each of them in their own column:

[TABLE="width: 397"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]apples[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]d[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]cranberries[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]pears[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lemons[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]nuts[/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can do this in VBA or as macros/formulas; I really don't care. The real table is very large with about 21K items in the original table with about 3000 rows.

Any ideas would be greatly appreciated.

 

[Weazel]

maybe something like...

Unknown[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA"]cranberries[/TD]
[TD="bgcolor: #FAFAFA"]pears[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]pears[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA"]oranges[/TD]
[TD="bgcolor: #FAFAFA"]lemons[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"]cranberries[/TD]
[TD="bgcolor: #FAFAFA"]oranges[/TD]
[TD="bgcolor: #FAFAFA"]lemons[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]e[/TD]
[TD="bgcolor: #FAFAFA"]lemons[/TD]
[TD="bgcolor: #FAFAFA"]pears[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA"]lemons[/TD]
[TD="bgcolor: #FAFAFA"]pears[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[TD="bgcolor: #FAFAFA"]nuts[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA"]oranges[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]apples[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]d[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]cranberries[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]pears[/TD]
[TD="bgcolor: #FAFAFA"]a[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]e[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]oranges[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]lemons[/TD]
[TD="bgcolor: #FAFAFA"]b[/TD]
[TD="bgcolor: #FAFAFA"]c[/TD]
[TD="bgcolor: #FAFAFA"]e[/TD]
[TD="bgcolor: #FAFAFA"]f[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA"]nuts[/TD]
[TD="bgcolor: #FAFAFA"]g[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$1:$A$7,SMALL(IF($B$1:$F$7=$A9,ROW($A$1:$A$7)-ROW($A$1)+1),COLUMNS($B9:B9))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[ffinlay]

That is awesome. I've tried array formulas before, but am not all that experienced with them. The expanding search array using the columns function is pretty interesting. I'd like to understand though, what does the Small() function provide in this solution?