Remove Duplicate using Formula

[cliqck]

Hi,
Could anybody help me with this by using formula?
Basically I would like to remove duplicates from a list, but using formula only.

Input:
Red
Red
Blue
White
White
Red
Black

Result:
Red
Blue
White
Red
Black

[bebo021999]

Assum your list in A2:A8
In B2 draging down

Please Login or Register  to view this content.

[menem]

Please try this formula pattern

=IFERROR(INDEX($D$5:$D$12,AGGREGATE(15,6,(ROW($D$5:$D$12)-ROW($D$4))/(1/(COUNTIF(E$5:E5,$D$5:$D$12)=0)),1)),"")

Regards.

[cliqck]

Hi Menem,
Thanks for the solution.
For some reason the formula doesnt work.
Any idea why?
Attachment 802592

[cliqck]

@bebo021999

Hi,
I notice that the formula using ROWS($1:1).
Unfortunately, my actual data between input and output are located in separated column and row (not side by side).
Is there any other solution?

[AliGW]

Menem's formula goes into D6, not D5.

Unfortunately, my actual data between input and output are located in separated column and row (not side by side).

Provide a sample workbook instead of leaving us guessing. Do this NOW.

[cliqck]

Thanks all.

Sorry for the confusion
Pls refer to attached sample file.

[bebo021999]

Your new sample quite different with 1st post ("Red" repeat twice) that why my solution in #2 did not work for new sample.
Try again:
In D5:

Please Login or Register  to view this content.

Drag down

[AliGW]

In D5 copied down:

=IFERROR(1/(1/INDEX($A$5:$A$16,MATCH(0,INDEX(COUNTIF($D$4:D4,$A$5:$A$16),0,0),0))),"")

[cliqck]

@AliGW

Thanks alot!
This could do the trick.

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.