Removing Duplicate Records to separate column

**matps** · 05-05-2022, 07:59 AM

Hi guys,

So I've got a question about extracting duplicate records and moving them to another column.

In my spreadsheet I have a column of 2700 codes which as it transpires have some duplicate codes in them. I was able to use the Data > Remove Duplicates to get rid of 150 dupes, but it now turns out I need to know what those 150 were to see how they got there in the first place!

Product code
ACLD0329P-J(04/21)
ACLD0329Z-E(04/21)
ACLD032R-F(04/21)
ACLD0349-A
ACLD034-B
ACLD034-B

so in the example above you'll see that ACLD034-B is repeated, so I'd like if possible is to extract that duplicate value (and the others in the column) to it's own column.

Hope this makes sense!

**WideBoyDixon** · 05-05-2022, 08:02 AM

You could try like this, adjusting the A1:A7 range to fit your values:

Please Login or Register  to view this content.

WBD

**AliGW** · 05-05-2022, 08:06 AM

I woud NOT recommend full column references, but adjust to suit!

For the duplicate list:

=UNIQUE(FILTER(A:A,COUNTIF(A:A,A:A)>1))

For the unique list:

=UNIQUE(FILTER(A:A,A:A<>""))