Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for extra row (2003)

    O.k...I have 2 spreadsheets with the same data on them. However 1 has an extra row of data that shouldnt be there.
    Is there a quick way I can get excel to pick out the rows from spreadsheet A that aren't also in spreadhseet B - therefore highlighting the "rogue data"
    Many Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for extra row (2003)

    Is there a column or combination of columns that uniquely identifies the rows?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking for extra row (2003)

    I can see several approaches, depending on the data. A simple approach is using MATCH (with a "match type" of zero to find exact matches)

    If you have a unique value in each row, you can use MATCH to look for this value in the appropriate column of the other sheet. Any MATCH yielding an NA error is not in the other sheet....

    If you must match several columns, you could concantenate several columns in each sheet to make a unique column and then do a match with each of these columns on the concatenation in the other sheet.

    Steve

  4. #4
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for extra row (2003)

    how would i use match?
    Essentially each row corresponds to a persons results on a series of tests.
    One spreadsheet has 528 rows, one has 527. but the issue is that the rows aren't necessarily in the same order in both spreadsheets so its not just an additional row tacked on to the bottom.
    I want to find the row (i.e set of test results) that aren't in the other spreadsheet.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for extra row (2003)

    Is there a column with the name of the person, or an ID number or something like that?

  6. #6
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for extra row (2003)

    i managed to do it based on match. what i did was i summed all their test results (on th assumption that this would give the individual a unique score, likely as i have 6 results some of them scored down to the decimal level) then i compared the 2 sum columns using match and found the one that came out as N/A. so that was good. then when i looked at the original database I could pick them out. Many thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for extra row (2003)

    Glad you were able to solve it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •