Remove duplicates & create list

[drluke]

I have a long list with codes in sheet 1 of my data starting at A2 (the list is very long 4500 rows):

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]C56[/TD]
[/TR]
[TR]
[TD]A125[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
[TR]
[TD]C56[/TD]
[/TR]
[TR]
[TD]B125[/TD]
[/TR]
[TR]
[TD]B125[/TD]
[/TR]
[TR]
[TD]A125[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
[TR]
[TD]D512[/TD]
[/TR]
[TR]
[TD]S226[/TD]
[/TR]
[TR]
[TD]D512[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
</tbody>[/TABLE]

In sheet 2 I need to create a list of all the codes and remove the duplicates. I'm using the following array formula to try and do it:

{=INDEX('Sheet 1'!$A$2:$A$4568,MATCH(0,COUNTIF('Sheet 1'!$A$1:A1,'Sheet 1'!$A$2:$A$4568),0))}

The formula results in the 1st and 2nd unique codes to be returned correctly but the 3rd unique code is duplicated for the rest of the lines:

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Code[/TD]
[/TR]
[TR]
[TD]C56[/TD]
[/TR]
[TR]
[TD]A125[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
[TR]
[TD]R89[/TD]
[/TR]
</tbody>[/TABLE]

I cannot work out what is wrong with the formula?

 

[bosco_yip]

Maybe,

In "Sheet 2" A2, non-CSE formula copied down :

=IFERROR(INDEX('Sheet 1'!$A$2:$A$4568,MATCH(0,INDEX(COUNTIF('Sheet 2'!$A$1:A1,'Sheet 1'!$A$2:$A$4568&""),0),0))&"","")

Regards
Bosco

 

[drluke]

Thank you for this. I get the same result as before using this formula.