Excel - Seperating / Showing Deleted Duplicated Data

Question

I have two columns, ID and ID's data.

ID has duplicate values that need to be seperated (not deleted). Specifically, rows with data B2 and A2. Indicated by yellow highlights in table below

Answer 1

Sort data if needed, as "removed duplicates" removes duplicates further down list. Autonumber a helper column.

Copy over this table to columns F,G,H, and select the table. Go to DATA → Remove Duplicates → select "id" as the column

Add this formula to column D. It helps identify deleted duplicates.

=IF(ISNA(VLOOKUP(A2,$F$1:$F$100,1,0)),"No","Yes")

Filter Data in Column D for values of "No".

Highlight contents, press alt + ;, press CTRL + C

Paste into new columns

Remove Data Filter

final data view

Highlighted data is the values we seperated.

Data views:

• Columns A,B,C are raw data
• Columns F,G,H are all unique values, specified by the way you sorted (duplicates further down list are seperated)
• Columns K,L,M show duplicated values that were removed

summary

This is a useful way to see what redundant data you have VS just flat out deleting duplicates, without using overly complex excel formulas, python pandas, sql queries, etc.

Just plain portable excel. You can automate it later by recording a macro or using a preexisting excel template.

How you sort the data affects what duplicates you get though. If you sorted from "Z-A" in the first image example, you would've have seperated out "A1" and "B1" instead.

Because duplicates further down column A are seperated.

Useful Link I used from wizard of excel