IFERROR formula help

[Pottie8]

Hi All

I have a column (in sheet called worksheet column E - starting in row 2) with all names, including duplicates.

I wish to make a list in a seperate sheet in column A with only the unique values (each name once in this list - starting in row 6). This list must also look at the value in worksheet column F (starting in row 2) and only build this list if this value is matched to the A1 of the new worksheet.

This is my current attempt at it:

=IFERROR(INDEX(Worksheet!$E$2:$E$16;MATCH(0;COUNTIF(Worksheet!$F$1:F1;Worksheet!$E$2:$E$16);0));"")

Quick Demo:

Worksheet

	E	F
	NAME	GROUP
2	John	Apple
3	John	Apple
4	Mary	Apple
5	Luke	Apple
6	Luke	Apple
7	Luke	Apple
8	James	Apple
9	James	Apple
10	Paul	Apple
11	Jason	Pear
12	Jason	Pear
13	Jason	Pear
14	Jason	Pear
15	Peter	Pear
16	Peter	Pear

<tbody>
</tbody>

Seperate Sheet

	A
1	APPLE
2
3
4
5
6	John
7	Mary
8	Luke
9	James
10	Paul
11
12
13
14
15
16
17

<tbody>
</tbody>

Hope this makes sense and someone has got some input for me


Thank you in advance.


Regards,
Pottie8

 

[Marcelo Branco]

Try this array formula in A6 copied down
=IFERROR(INDEX(Worksheet!$E$2:$E$16,MATCH(0,(Worksheet!$F$2:$F$16<>A$1)+COUNTIF(A$5:A5,Worksheet!E$2:E$16),0)),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Pottie8]

THANK YOU SO MUCH!

And for the prompt response!!

Works a charm!


Regards,
Pottie8

 

[Marcelo Branco]

You are welcome. Glad to help

M.