Intersection of table columns

[jahsquare]

Hi, I searched the forum but wasn't able to find an answer to my question:

If I have a table (formatted as a table) with the following data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Boy[/TD]
[TD]Girl[/TD]
[TD]Human[/TD]
[TD]Adult[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Boaz[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

How can I generate the following table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Boy[/TD]
[TD]Girl[/TD]
[/TR]
[TR]
[TD]Human
[/TD]
[TD]Boaz
Jack
[/TD]
[TD]Jill[/TD]
[/TR]
[TR]
[TD]Adult[/TD]
[TD]Boaz[/TD]
[TD]Jill[/TD]
[/TR]
</tbody>[/TABLE]


Where the values are sorted and represent the column intersections from the source table.

Alternatively,
How could I generate this table:

	Boy	Girl
Human	2	1
Adult	1	1

<tbody>
</tbody>

Where selection of the numbers in the table would allow a user to access the corresponding list value from the table above (like a pivot table)? I am working with a large data set w/many columns so I would live to avoid using helper-columns if possible.

Thanks, just can't seem to figure this one out...

 

[jahsquare]

Ok so still having some trouble.. So if I use the formula below in H2 of your example, can I somehow use TEXTJOIN and an IF to display the concatenated values from the ID column?

=SUMPRODUCT(
--(INDIRECT("Table2["&H$1&"]")<>""),
--(INDIRECT("Table2["&$G2&"]")<>"")
)

Also, am still hopeful to find a solution similar to option A (in my response up there to MickG)

 

[Eric W]

Yes, you can use INDIRECT and table references. The H2 formula would look like:

=TEXTJOIN(",",TRUE,IF((INDIRECT("Table1["&H$1&"]")<>"")*(INDIRECT("Table1["&$G2&"]")<>""),Table1[ID],""))

However, I might suggest using INDEX/MATCH instead of the volatile INDIRECT, which would look like this:

=TEXTJOIN(",",TRUE,IF((INDEX(Table1,0,MATCH(H$1,Table1[#Headers],0))<>"")*(INDEX(Table1,0,MATCH($G2,Table1[#Headers],0))<>""),Table1[ID],""))