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):

C56

A125

R89

C56

B125

B125

A125

R89

D512

S226

D512

R89

<tbody>
</tbody>

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:

Code

C56

A125

R89

R89

R89

<tbody>
</tbody>

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.