Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-25
5
Medium Priority
?
206 Views
Last Modified: 2014-10-28
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
0
Comment
Question by:PDSWSS
  • 3
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 40404167
Copy all values to column C, and use the Remove Duplicates function on the Data tab.
0
 

Author Comment

by:PDSWSS
ID: 40404176
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.
0
 

Author Closing Comment

by:PDSWSS
ID: 40404345
Thanks for the quick solution.
0
 
LVL 24

Expert Comment

by:Ejgil Hedegaard
ID: 40405100
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.
0
 

Author Comment

by:PDSWSS
ID: 40409342
Thank you very much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question