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
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NAME[/TD]
[TD]GROUP[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]John[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]John[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Mary[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Luke[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Luke[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Luke[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]James[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]James[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Paul[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Jason[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Jason[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Jason[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Jason[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Peter[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Peter[/TD]
[TD]Pear[/TD]
[/TR]
</tbody>[/TABLE]

Seperate Sheet
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]APPLE[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Luke[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]James[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.