Compare 2 spreadsheets and highlight differences
Page 1 of 10 123 ... LastLast
Results 1 to 10 of 100

Thread: Compare 2 spreadsheets and highlight differences

  1. #1
    Board Regular
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Compare 2 spreadsheets and highlight differences

     
    I did a search for compare threads and there are numerous posts but I didn't manage to find one of the more simpler tasks covered - I'm sure they are hiding in there somewhere! If you can point me towards one that meets my needs or if you can suggest a solution via Reply post I'd be very grateful!

    I've 2 spreadsheets as follows:

    A) cols A to Z (a master doc that requires updating weekly)
    B) cols A to G (updates downloaded from a work database) - all 7 cols have equivalent cols in sheetA though not adjacent

    One of the cols in A and B contains the key field that's used to check for a match between the 2 sheets (in SheetA it's Col B and in SheetB it's Col A).

    There could be over 4,000 records/rows in SheetA but SheetB will typically have less than 500 rows. I want to run a compare between the 2 sheets weekly and:


    1. Check each record in SheetB (ColA) against all of the records in SheetA (ColB) based on the key field.
    2. If there's no matching key in the weekly download (SheetB-ColA), anywhere in SheetA (ColB), then there is no updating to be done for that row and move on to the next record in B.
    3. Where a match is found on the key field in SheetA and SheetB, then the row in SheetB is to be highlighted (ideally the rows would be copied to a new worksheet as a record of that week's changes). Then, the other 6 fields in SheetB would replace the corresponding 6 fields in SheetA, even if only one of the fields is different. For the 6 non-key fields, let's assume that SheetB_Cols B to G correspond with SheetA_Cols C, E, G, I, K, M


    I hope I've explained what's required reasonably clearly. I've used the terms record/row and field/cell interchangeably. The spreadsheets have yet to be created so I can't provide samples. However, I know the cols to be matched won't appear in the same order and I've used an illustrative example. To summarise the above:

    If cell A1 in SheetB matches any ColB cell in Sheet A, then the data in B1, C1, D1, E1, F1 and G1 is highlighted and cells in the corresponding SheetA row are replaced by the SheetB data.

    I look forward to any guidance you may be able to provide.

    Many Thanks!
    Cyril

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    tomato large 25 pepper large 32 X
    lettuce large 39 tomato medium 20 ITEMS MARKED X FOR CLARITY
    pepper large 32 tomato small 19 X need to be updated in main table
    tomato medium 20 lettuce small 29
    lettuce medium 32 pepper small 24 X
    pepper medium 27
    tomato small 19
    lettuce small 29
    pepper small 24
    original main table
    tomato large 25
    this macro has updated changed prices lettuce large 39
    pepper large 30
    Sub Macro5() tomato medium 20
    ' lettuce medium 32
    ' Macro5 Macro pepper medium 27
    ' Macro recorded 27/10/2017 by bob tomato small 18
    ' lettuce small 29
    pepper small 25
    '
    For j = 1 To 5
    For k = 1 To 9
    If Cells(k, 1) = Cells(j, 9) Then GoTo 20 Else GoTo 99
    20 If Cells(k, 2) = Cells(j, 10) Then GoTo 70 Else GoTo 99
    70 If Cells(k, 3) <> Cells(j, 11) Then GoTo 80 Else GoTo 99
    80 Cells(k, 3) = Cells(j, 11)
    99 Next k
    Next j
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    Thanks Oldbrewer for taking the time to post your reply. Unfortunately, I'm not able to follow - can you elaborate please?

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    the macro checks every price and updates where necessary. What do you want, then ?

  5. #5
    Board Regular
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    Thanks again .... I accept it's not so easy to convey the requirement via text alone so I've spent a good bit of time creating a demo doc that relates to my original post. The only additional point is that a record in SheetB will only ever find a single match in the master doc SheetA. Please see if you can access this public doc (link) and if it makes things clearer for you. I hope so anyway It may look busy at first but there's very little going on in reality as only 2 records can be matched across the 2 sheets and they are all that we need to focus on. If these can be isolated in a separate sheet after the updating is finished it would be a bonus i.e. not essential.

    P.S. I did try using one of the recommended add-ins but it distorted col widths, some formats and dropped text boxes when I tried on the test site)

    P.S. #2 ..... what's your opinion of Vlookup as an alternative option?

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    SHEET-A (Master Doc)
    Value1 KeyField Type Value-X Value2 Value-Y Date1 Date-X Date2 Date-Y Size1 Size-X Size2 Size-Y
    55 122 1a 3 20 888 22-Mar 01-Jan 04-Jun 01-Nov AA B1 GG C6
    999 124 2b 4 888 889 22-Mar 02-Jan 17-Jun 02-Nov AB B2 GGGG C7
    57 130 3c 5 30 890 24-Mar 03-Jan 06-Jun 03-Nov AA B3 GG C8
    57 135 4d 6 333 891 12-Feb 04-Jan 25-Mar 04-Nov AAAAAA B4 GG C9
    59 149 5f 7 30 892 26-Mar 05-Jan 08-Jun 05-Nov AA B5 GG C10
    60 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a
    61 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a
    62 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a
    SHEET-B (Downloaded file for matching against the Master Doc)
    KeyField Value1 Value2 Date1 Date2 Size1 Size2 this macro has detected
    123 n/a n/a n/a n/a n/a n/a
    124 999 888 22-Mar 17-Jun AB GGGG B18=999 C18 = 888 E18=17-Jun G18=GGGG
    125 n/a n/a n/a n/a n/a n/a
    126 n/a n/a n/a n/a n/a n/a
    30 n/a n/a n/a n/a n/a n/a
    135 57 333 12-Feb 25-Mar AAAAAA GG C22=333 D22=12-Feb F22=AAAAAA
    138 n/a n/a n/a n/a n/a n/a
    140 n/a n/a n/a n/a n/a n/a
    and has coloured each of those cells yellow
    and updated the master table at the top
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 28/10/2017 by bob
    '
    '
    For j = 17 To 24
    For k = 5 To 12
    If Cells(j, 1) = Cells(k, 2) Then GoTo 20 Else GoTo 50
    20 y = -1
    For z = 2 To 7
    22 y = y + 2
    If y = 3 Then GoTo 22
    If Cells(j, z) = Cells(k, y) Then GoTo 40
    Cells(k, y) = Cells(j, z)
    Cells(j, z).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    40 Next z
    50 Next k
    Next j
    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    Thanks a lot Oldbrewer! Sorry about the delay in responding following a public holiday here on Monday and work priorities in meantime.

    As one that is only broadly familiar with macros, can I ask where in the macro the 2 separate sheets are referenced (In my sample, I had included both in the one tab just for ease of viewing)? If I used 2 spreadsheets rather than 2 worksheets, what area of the macro would need adjusting? Is the macro stored in one spreadsheet or more widely available to all?

    The results seem to be along the lines I'm looking for, even if the colour isn't showing on the post, so I'm keen to check out. All going well I'm hoping I won't be troubling you much further on this.

    Much obliged - Cyril

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    I usually start with all data on one sheet to sort out the approach. Then when you the client say it works and now can we take account of different sheets I finish it off. Is it 2 sheets in the same workbook ie sheet1 is the master and sheet 2 the updates sheet ?

    I am retired, aged 72, and helping people on here keeps my brain ticking over, and so far I have never watched daytime TV.....

    Bob

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,178
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

    Value1 KeyField Type Value-X Value2 Value-Y Date1 Date-X Date2 Date-Y Size1 Size-X Size2 Size-Y KeyField Value1 Value2 Date1 Date2 Size1 Size2
    55 122 1a 3 20 888 22-Mar 01-Jan 04-Jun 01-Nov AA B1 GG C6 123 n/a n/a n/a n/a n/a n/a
    999 124 2b 4 888 889 22-Mar 02-Jan 17-Jun 02-Nov AB B2 GGGG C7 124 22222 33333 22-Mar 17-Jun ABAB GTGT
    57 130 3c 5 30 890 24-Mar 03-Jan 06-Jun 03-Nov AA B3 GG C8 125 n/a n/a n/a n/a n/a n/a
    57 135 4d 6 333 891 12-Feb 04-Jan 25-Mar 04-Nov AAAAAA B4 GG C9 126 n/a n/a n/a n/a n/a n/a
    59 149 5f 7 30 892 26-Mar 05-Jan 08-Jun 05-Nov AA B5 GG C10 MATCHING SHEET 30 n/a n/a n/a n/a n/a n/a
    60 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a TO SEE 135 57 77777 12-Feb 25-Mar TTTTT SSSSS
    61 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a WHICH VALUES 138 n/a n/a n/a n/a n/a n/a
    62 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a ARE TO 140 n/a n/a n/a n/a n/a n/a
    BE CHANGED
    this is the first sheet, master
    the second sheet is matching
    this macro is run from within matching sheet
    IT DOES NOT WORK
    BUT I CANNOT WORK ON IT FOR 5 DAYS
    MAYBE YOU LOOK AT IT AND TRY TO SORT IT
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 28/10/2017 by bob
    '
    '
    Dim mymatch(10, 10)
    For j = 2 To 9
    Sum = Sum + 1
    For k = 1 To 7
    mymatch(Sum, k) = Cells(j, k)
    Next k
    Next j
    Sheets(1).Select
    For p = 2 To 9
    For r = 1 To 13 Step 2
    If r = 3 Then GoTo 90
    rr = rr + 1
    tot = tot + 1
    If Cells(p, 1) = mymatch(p - 1, tot) Then GoTo 80 Else GoTo 100
    80 If Cells(p, r) <> mymatch(p - 1, rr) Then GoTo 85 Else GoTo 90
    85 Cells(p, r) = mymatch(p - 1, rr)
    Sheets(2).Select
    Cells(p - 1, rr).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Sheets(1).Select
    90 Next r
    100 Next p
    End Sub

  10. #10
    Board Regular
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare 2 spreadsheets and highlight differences

      
    Quote Originally Posted by oldbrewer View Post
    Value1 KeyField Type Value-X Value2 Value-Y Date1 Date-X Date2 Date-Y Size1 Size-X Size2 Size-Y KeyField Value1 Value2 Date1 Date2 Size1 Size2
    55 122 1a 3 20 888 22-Mar 01-Jan 04-Jun 01-Nov AA B1 GG C6 123 n/a n/a n/a n/a n/a n/a
    999 124 2b 4 888 889 22-Mar 02-Jan 17-Jun 02-Nov AB B2 GGGG C7 124 22222 33333 22-Mar 17-Jun ABAB GTGT
    57 130 3c 5 30 890 24-Mar 03-Jan 06-Jun 03-Nov AA B3 GG C8 125 n/a n/a n/a n/a n/a n/a
    57 135 4d 6 333 891 12-Feb 04-Jan 25-Mar 04-Nov AAAAAA B4 GG C9 126 n/a n/a n/a n/a n/a n/a
    59 149 5f 7 30 892 26-Mar 05-Jan 08-Jun 05-Nov AA B5 GG C10 MATCHING SHEET 30 n/a n/a n/a n/a n/a n/a
    60 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a TO SEE 135 57 77777 12-Feb 25-Mar TTTTT SSSSS
    61 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a WHICH VALUES 138 n/a n/a n/a n/a n/a n/a
    62 n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a ARE TO 140 n/a n/a n/a n/a n/a n/a
    BE CHANGED
    this is the first sheet, master
    the second sheet is matching
    this macro is run from within matching sheet
    IT DOES NOT WORK
    BUT I CANNOT WORK ON IT FOR 5 DAYS
    MAYBE YOU LOOK AT IT AND TRY TO SORT IT
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 28/10/2017 by bob
    '
    '
    Dim mymatch(10, 10)
    For j = 2 To 9
    Sum = Sum + 1
    For k = 1 To 7
    mymatch(Sum, k) = Cells(j, k)
    Next k
    Next j
    Sheets(1).Select
    For p = 2 To 9
    For r = 1 To 13 Step 2
    If r = 3 Then GoTo 90
    rr = rr + 1
    tot = tot + 1
    If Cells(p, 1) = mymatch(p - 1, tot) Then GoTo 80 Else GoTo 100
    80 If Cells(p, r) <> mymatch(p - 1, rr) Then GoTo 85 Else GoTo 90
    85 Cells(p, r) = mymatch(p - 1, rr)
    Sheets(2).Select
    Cells(p - 1, rr).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Sheets(1).Select
    90 Next r
    100 Next p
    End Sub
    Oldbrewer, people like you are so generous with your time and talents!!

    I will await your return rather than dabble in something I'm pretty clueless about. For instance, I don't know if 'Sheets(2)' refers to the 'master' sheet or not .... if not, then where is the link?

    On reflection, I think I will opt for 2 completely separate spreadsheets (workbooks) - you can refer to them as Sheet-A (master) and Sheet-B (download doc) to keep aligned with the text in posts and in the sample doc here.

    'til the next time then ..... take care!
    Cyril

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com