id="info"

Question

Locked

excel 2007

By sallydj ·
hi i want to compare sheets in one excel 2007 file for duplicate names to see if there are any, how do i do this anyone???

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

try a formula

by databaseben In reply to excel 2007

what you can do within a worksheet is to first add a column and give each line a physical line number. the line number will help maintain the order of the list.

the next step is to sort the worksheet by the name column alphabetically.

the next step is to create a formula in one of the last blank columns to compare the names.

the formula is basically a if / then scenario which goes like this:

if current line / name equals next line / name then 1; if not then 0

afterwards copy that formula down to the last row.

next convert that formula column into values in order to remove the fluid equation but leave the values of 1's and 0's

next sort that column of 1s and 0s, then delete all lines that are flagged with a 1

next resort your worksheet via your line number column.

----------- some notes --------------

remember to make a copy of your excel file before doing the above because you may need to retry the methodology a few times before you get it right.

also, the methodology is for one worksheet in your file. you would have to merge all the data from the other worksheets into one sheet, then use the formula above.

so just like creating a line number mentioned above, you should also create a column in the other worksheets that id's the data to its home worksheet, before moving the data into a single sheet for processing.

after you run the formula, then resort the data by the id's and cut and paste your cleaned up data back to their home sheets.

finally, you can also use msaccess to link to the excel tables or import the tables to look for duplicates as well.

Collapse -

use Vlookup function

by markp24 In reply to excel 2007

you can use the vlookup function to mach data between sheets (helook up also)

Collapse -

Search for duplicate values

by Al Kostiuk In reply to excel 2007

You can use a Pivot table that displays a list of the values with a count of those values. You will see a count greater than 1 where duplicates exist.

Back to Software Forum
4 total posts (Page 1 of 1)  

Software Forums