+ Reply to Thread
Results 1 to 2 of 2

Comparing Data

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Comparing Data

    I have two spreadsheets of information that need to be compared against each other. The first sheet contains the original data. The second sheet contains the same information as found in the first but less certain records that should not belong...my IT guys had originally dumped data into a new system but were pulling data from two sources. My job is two figure out which records were deleted from the second dump. Each record from each spreadsheet has it's own unique key. Unfortunately, I can not use the key for comparisons because every time we rerun the program the key number changes.

    The first spreadsheet for example is as follows:

    Key Product Color Size City
    1 Apple Green Small Atlanta
    2 Apple Green Medium Houston
    3 Apple Green Medium Las Vegas
    4 Apple Green Large Atlanta
    5 Apple Green Large Atlanta
    6 Apple Red Small New York
    7 Apple Red Medium Atlanta
    8 Apple Red Medium Madison
    9 Apple Red Large Las Vegas
    10 Apple Red Large Las Vegas

    The second spreadsheet for example is as follows:

    Key Product Color Size City
    11 Apple Green Small Atlanta
    13 Apple Green Medium Las Vegas
    14 Apple Green Large Atlanta
    16 Apple Red Small New York
    17 Apple Red Medium Atlanta
    19 Apple Red Large Las Vegas
    20 Apple Red Large Las Vegas

    I need to put an indicator in the first spreadsheet to indicate if the record was found or removed in the second sheet (i.e. 1=found, 2=not found). For example: record 1=1; record 2=2, record 3=1; record 4=1.....and so on. The problem I may run into however is that records 4/5 and records 9/10 are exactly the same. When I come to record 5, the program is going to find the same information in the second set of data but record 4 has already used this record and I do not want to use it again.....record 5 should = 2 (not found). As for records 9 & 10, both should have indicator = 1, as both were found in second spreadsheet.

    The final output should be as follows: record 1=1; record 2=2, record 3=1; record 4=1, record 5=2, record 6=1, record 7=1, record 8=2, record 9=1, record 10=1

    Thanks in advance for any comments.

  2. #2
    Forum Contributor
    Join Date
    02-28-2006
    Posts
    690
    Go through your first spreadsheet and remove duplications.

    f1=concatenate(a1,b1,c1,d1,e1) and copy down

    in g2 use this formula

    =if(f2=f1,1,0)

    this marks duplications with a 1

    highlight column g, then edit,copy,paste, special,values

    then sort column g into descending order, and delete all rows coded 1

    Now you can test for duplications in the second spread sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1