asked on

How would I identify each unique subject ID in the attached excel spreadsheet.

I have an excel spreadsheet with several thousand subject ID entrees in column A and most are repeated many times.

How would I extract the unique subject IDs into a separate column C in the excel spreadsheet? Please see attached example.

Thanks,
EE.xlsx

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PDSWSS

ASKER

Thank you. You answered my question and will give you the points.
However, I should have been clearer. I wanted to delete the rows that included the duplicate subject IDs as well.

Could you answer this one or should it be a separate question. Thanks again.

PDSWSS

ASKER

Thanks for the quick solution.

Ejgil Hedegaard

Use the Remove Duplicates on column A instead.

Or insert this formula in B2 =IF(COUNTIF($A$2:A2,A2)>1,"Remove","Keep")
Copy down, double click on the black square in the lower right corner of the cell marker.

Mark result in column B.
Copy and paste as values (=Right click somewhere in column B, select Insert special in the menu, select values, Ok)
Sort on column B, to get "Remove" at the bottom of the list.
Then delete all with "Remove" in column B.
Delete the result "Keep" in column B.

You can also filter on column B to show only "Remove", select all rows, (entire rows) in the list, right click and select Delete rows.
Delete the formulas in column B.

PDSWSS

ASKER

Thank you very much.