How to find duplicate information in excel?

Question

Hello, I am trying to find duplicate information in excel workbook.

I am working for a company that has 50 stores. I made a excel worksheet for each store with employees names and ssn. Now I am trying to see if any employee is in more than one store [in more than 1 worksheet]

all the stores [sheets] are in 1 workbook.
How can I do that.

Answer 1

A database like Access or Foxpro is more suited for what you're trying to do. Excel is a good spreadsheet, but a poor database.

Querying a database is easy, querying a spreadsheet is harder.

Answer 2

As far as I know, you can only do this in one sheet at a time in Excel, so you may have to combine data from all sheets into one sheet.

Use Microsoft Excel Help and type in the phrase "Filter for unique records" and it will show you how to do this in one sheet.




If you know how to use Microsoft Access, you can import your data into a database. When you create a table in Access, each record has to be given a unique identifier.

For instance, in a database of employees, the unique identifier can be the SSN (since every employee will have an SSN unique to them). Once it is set up, Access will not allow you to enter another record with the same unique identifier (in this case, SSN), thus preventing duplicate records.

If you don't know Access and you'll have to be updating employee info often, I highly recommend learning Access or getting someone to set it up for you. It is relatively easy to set it up, but it's even easier to maintain. It will same you time in the long run.


(Edited to add: Actually, CMC1217's idea about Pivot Tables could work for multiple sheets if you know how to use it. I'm not sure if the subtotal function will work across multiple sheets. I still think it will be more efficient in the long run to convert to Access.)

Answer 3

Whilst the previous answers are accurate, they don't solve your underlying problem. You see, the way you have arranged your data makes it incredibly difficult to make the comparisons you need in order to find duplicates. Sure, 50 different worksheets makes sense because you have 50 stores. But that means you would need a formula or macro that checks 49 other worksheets to make the comparisons and denote duplications.

To TRULY solve your problem, you need to rearrange your data so that formulae can be used in comparative statements to find duplications. Here's what I recommend you do:

Combine all data into a single sheet using three columns:
Column_A: Store_Name or Store_Number
Column_B: Employee_Name
Column_C: SS_N

You should be able to copy and paste all the data from the fifty workbooks rather quickly. Use the "copy down" feature so you don't need to retype the store_name repeatedly.

Now in Column D, use the following formula, starting in D2 and copy down as many rows as columns A,B,C. This will tell you if any of the names are duplicated:

=IF (COUNTIF (B:B, B2)>1, "Duplicate", "")


Now in Column E, use the following formula, starting in E2 and copy down as many rows as columns A,B,C. This will tell you if any of the SSN#'s are duplicated:

=IF (COUNTIF (C:C, C2)>1, "Duplicate", "")

That's it. Wherever you see the word "Duplicate" you know that name or SSN appears elsewhere in the column. If you want an actual example sheet demonstrating this method, just send me your email address.

Hope this helps.

Regards,
QwertyKPH @ Yahoo!

Answer 4

try a pivot table - use the employee name or SSN as left side field and put a count in values.

If you're not familiar w/ pivot tables - try sorting by employee name or SSN and then use the subtotal function.

Search for anything higher than 1 either way you do it.

Answer 5

The short answer is there's no easy way. I could give you some gibberish about Perl scripts and CSV files, but unless you're already a database administrator, you're probably not going to have a clue what I'm talking about.

Otherwise, you're looking at a search by hand for every field you care about, and I imagine you already tried this for a few minutes (maybe a few hours?) before posting your question here.

Answer 6

hm the only way i know is to sort them, so all the like items stay together...then you would have to scan the rest of the document for any doubles.

Answer 7

go to "find" on each worksheet. might be under "replace" or "find and replace"