find column value

[tonyrensya]

Hello all,

I have a list of data in the table 1, how can I get the data list in Table 2 with formula?
I can use excel 2019 or google sheet. Thank you!

Table 1	1	2	3
Apple	Tom	Peter
Orange	John	May	Peter
Banana	Tom	May	John

Table 2
Tom	Apple	Banana
John	Orange	Banana
May	Orange	Banana
Peter	Apple	Orange

 

[duggie33]

This will work in Office 365...

Book_2023-10-03.xlsx
	A	B	C	D
1	Table 1	1	2	3
2	Apple	Tom	Peter
3	Orange	John	May	Peter
4	Banana	Tom	May	John
5
6	Table 2
7	Tom	Apple	Banana
8	Peter	Apple	Orange
9	John	Orange	Banana
10	May	Orange	Banana
Sheet3

Cell Formulas
Range	Formula
A7:A10	A7	=UNIQUE(TOCOL(B2:D4,1))
B7:C10	B7	=TRANSPOSE(FILTER($A$2:$A$4,($B$2:$B$4=A7)+($C$2:$C$4=A7)+($D$2:$D$4=A7)))
Dynamic array formulas.

Hope that helps,

Doug