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:
<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
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:
<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
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.
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:
a | apples | cranberries | pears | ||
b | pears | apples | oranges | lemons | |
c | cranberries | oranges | lemons | ||
d | apples | ||||
e | lemons | pears | |||
f | apples | lemons | pears | ||
g | nuts | apples | oranges |
<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
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:
apples | a | b | d | f | g |
cranberries | a | c | |||
pears | a | b | e | f | |
oranges | b | c | g | ||
lemons | b | c | e | f | |
nuts | g |
<colgroup><col><col span="5"></colgroup><tbody>
</tbody>
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.