Extract a unique list of items with formula

[GerryZ]

Hi all
the table on the top you can see each person have some fruits
and on the bottom table I need to extract unique list of names for each fruit
thank you!

Excel Workbook
	A	B	C	D	E
1	FRANK	GEORGE	JULIE	MIKAEL	LUIS
2	APPLE	KIWI	APPLE	TANGERINE	KIWI
3	ORANGE	ORANGE	KIWI	TANGERINE	PEAR
4	ORANGE	ORANGE	ORANGE	TANGERINE	PEAR
5	APPLE	ORANGE	ORANGE	ORANGE	PEAR
6	PEAR	ORANGE	ORANGE	ORANGE	KIWI
7	PEAR	ORANGE	ORANGE	ORANGE	KIWI
8		KIWI
9
10
11	RESULT
12	APPLE	ORANGE	PEAR	KIWI	TANGERINE
13	FRANK	FRANK	FRANK	GEORGE	MIKAEL
14		GEORGE	LUIS	JUILIE
15		JULIE		LUIS
16		MIKAEL
17
Sheet

 

[Mike Szczesny]

Hi GerryZ,

You can use this us formula at A13[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$E$1,SMALL(IF(FREQUENCY(IF($A$2:$E$8=A$12,MATCH($A$1:$E$1,$A$1:$E$1,0)),ROW($A$2:$E$8)-ROW($A$2)+1),ROW($A$2:$E$8)-ROW($A$2)+1),ROWS($A$13:A13))),"") Use C-S-E copy down. Then copy formula from A13 to B13, copy down. Repeat steps for columns C, D, and E for your results. Actually this formula finds apples for Frank and Julie. FYI: the frequency function removes duplicate names[/TD]
[/TR]
</tbody>[/TABLE]

 

[GerryZ]

sorry!! errata corrige
this is the end result
thank you!

Excel Workbook
	A	B	C	D	E
1	FRANK	GEORGE	JULIE	MIKAEL	LUIS
2	APPLE	KIWI	APPLE	TANGERINE	KIWI
3	ORANGE	ORANGE	KIWI	TANGERINE	PEAR
4	ORANGE	ORANGE	ORANGE	TANGERINE	PEAR
5	APPLE	ORANGE	ORANGE	ORANGE	PEAR
6	PEAR	ORANGE	ORANGE	ORANGE	KIWI
7	PEAR	ORANGE	ORANGE	ORANGE	KIWI
8		KIWI
9
10
11	RESULT
12	APPLE	FALSO	PEAR	KIWI	TANGERINE
13	FRANK	FRANK	FRANK	GEORGE	MIKAEL
14	JULIE	JULIE	LUIS	JULIE
15		MIKAEL		LUIS
16		LUIS
17
Sheet

 

[GerryZ]

hi mike there is a problem i cannot read what you wrote
thank you anyway

 

[hiker95]

GerryZ,

Using Mike Szczesny's array formula:

Excel 2007
	A	B	C	D	E
1	FRANK	GEORGE	JULIE	MIKAEL	LUIS
2	APPLE	KIWI	APPLE	TANGERINE	KIWI
3	ORANGE	ORANGE	KIWI	TANGERINE	PEAR
4	ORANGE	ORANGE	ORANGE	TANGERINE	PEAR
5	APPLE	ORANGE	ORANGE	ORANGE	PEAR
6	PEAR	ORANGE	ORANGE	ORANGE	KIWI
7	PEAR	ORANGE	ORANGE	ORANGE	KIWI
8		KIWI
9
10
11	RESULT
12	APPLE	ORANGE	PEAR	KIWI	TANGERINE
13	FRANK	FRANK	FRANK	GEORGE	MIKAEL
14	JULIE	GEORGE	LUIS	JULIE
15		JULIE		LUIS
16		MIKAEL
17
Sheet1

Cell Formulas
Range	Formula
A13	{=IFERROR(INDEX($A$1:$E$1,SMALL(IF(FREQUENCY(IF($A$2:$E$8=A$12,MATCH($A$1:$E$1,$A$1:$E$1,0)),ROW($A$2:$E$8)-ROW($A$2)+1),ROW($A$2:$E$8)-ROW($A$2)+1),ROWS($A$13:A13))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the array formula into cell A13

Then copy A13 down to cell A17

Then copy A13:A17 To B13:E13

 

[GerryZ]

hiker 95
thank you for the answer but the result is wrong
here below you can see the result of your formula, which is not ta same of above
I need a unique list of persons that have the fruit.
thank you!

Excel Workbook
	A	B	C	D	E
12	APPLE	ORANGE	PEAR	KIWI	TANGERINE
13	GEORGE	FRANK	FRANK	GEORGE	MIKAEL
14	JULIE	GEORGE	LUIS	JULIE
15		JULIE		LUIS
16
17
Sheet

 

[GerryZ]

@Mike szczesnay
@Hiker95
thank you all works perfectly
verywell
in the mean time I found onther formula watch this!!

Excel Workbook
	A	B	C	D	E
1	FRANK	GEORGE	JULIE	MIKAEL	LUIS
2	APPLE	KIWI	APPLE	TANGERINE	KIWI
3	ORANGE	ORANGE	KIWI	TANGERINE	PEAR
4	ORANGE	ORANGE	ORANGE	TANGERINE	PEAR
5	APPLE	ORANGE	ORANGE	ORANGE	PEAR
6	PEAR	ORANGE	ORANGE	ORANGE	KIWI
7	PEAR	ORANGE	ORANGE	ORANGE	KIWI
8		KIWI
9
10
11	RESULT
12	APPLE	ORANGE	PEAR	KIWI	TANGERINE
13	FRANK	FRANK	FRANK	GEORGE	MIKAEL
14	JULIE	GEORGE	LUIS	JULIE
15		JULIE		LUIS
16		MIKAEL
17
18
Sheet

 

[hiker95]

GerryZ,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.