Questions

excel 2007

+
0 Votes
Locked

excel 2007

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???
  • +
    0 Votes
    databaseben

    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.

    +
    1 Votes
    markp24

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

    +
    0 Votes
    Al Kostiuk

    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.

  • +
    0 Votes
    databaseben

    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.

    +
    1 Votes
    markp24

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

    +
    0 Votes
    Al Kostiuk

    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.