Excel Guru's....Need some help with a situation

[leeland]

Hello,

well to get down to it, my co-worker has a list of over 19,000 records...they are Social Security numbers that she uses for a certain report.

Every month she has to re-run a different report to find new entries into the system. So every month there are roughly about 1,000 additional entries (or social security numbers)

She then has to go through and weed out any duplicate SS numbers...she used to do this in access but apparently got to troublesome...

Then tried some things in excel itself but felt it might have been missing blocks of info...

So she has been doing this manually which apparently takes almost a day to go through

she has it set up into two lists like this


Master List (ROWA) temp list (ROWB)

xxx-xx-xxxx xxx-xx-xxxx

she sorts them and checks manually, and once she is done deleting the duplicates out she adds the temp list to the bottom of the master list for the final report she uses...

about 70% of the temp list is repeats...



Do you know of a way I could help her automate this process in excel ? I am not that good but some recommendations might help....

I appreciate your help

Leeland

 

[WW]

so she wants only the unique records? excel can take a list and output unique values in that range by:

1. select the range (the column with ssns)
2. Data > Filter > Advanced Filter > Action Copy to another Location, and select a new column for 'copy to' box

3. check 'unique records only'

that will make a new list with no duplicates.


or if she wants to see the ssns matched up side by side:

have master list in column A, new list in B, and in C2 enter:

=VLOOKUP(B2,A:A,1,FALSE)

(and fill down by hovering over the little box square in the lower right hand corner of C2 that is selected with that formula, then double clicking on the little box)

that vlookup will compare the new values against the master list. If it's found in the master list, it will just repeat the value, if it's not in the master, a #N/A will show up. Do an autofilter select the #N/A rows.


with that much data it may be slow however...try it

 

[Concillian]

Originally posted by: WW
with that much data it may be slow however...try it

It will take one HELL of a lot less time than all day though.

 

[WW]

Originally posted by: Concillian

Originally posted by: WW
with that much data it may be slow however...try it

It will take one HELL of a lot less time than all day though.

Ha! exactly....and once you find a procedure that works, record the keystrokes as a macro (or write a vba script) and then it could be a one step operation.....start it and walk away

 

[NeoV]

I would do this for starters...

add the number 1 next to each of the entries, so column A is full of the SS #'s, and column B simply has a number 1 in each cell

Do a pivot table, summing the number field, and have it sort descending based on that number...your duplicates will appear at the top...


The advanced filter thing works very well too