Compare 2 spreadsheets and highlight differences - Page 4
Upcoming Power Excel Seminars
Page 4 of 10 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 100

Thread: Compare 2 spreadsheets and highlight differences

  1. #31
    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

     
    sorry for the delay I found this very tricky

    Con Number (key) Tr Code C Start Date Fin Date Approved Value Used Value Ven Numb Ven Name BU REF
    101 NNN ZZZ NNN PPP A A A A A A
    102 A A YYY A A A A A A A
    103 A A XXX VVV A A A A A A
    104 A A A A A A A A A A
    105 A A A A A A A A A A
    106 A A A A A A A A A A
    107 A A A A A A A A A A
    108 A A WWW TTT SSS RRR A A A A
    109 A A A A A A A A A A
    110 A A A A A A A A A A
    THE TABLE BELOW IS TODAYDATA.XLS AND FOR TEST PURPOSES A NUMBER OF A's WERE CHANGED
    Con Number (key) Tr Code C Start Date Fin Date Approved Value Used Value Ven Numb Ven Name BU REF
    101 NNN ZZZ NNN PPP A A A A A A
    102 A A YYY A A A A A A A
    201 A A A A A A A A A A
    202 A A A A A A A A A A
    103 A A XXX VVV A A A A A A
    203 A A A A A A A A A A
    204 A A A A A A A A A A
    108 A A WWW TTT SSS RRR A A A A
    205 A A A A A A A A A A
    206 A A A A A A A A A A
    207 A A A A A A A A A A
    110 A A A A A A A A A A
    this macro, run from todaydata spreadsheet, finds all discrepancies and updates yesterday spreadsheet
    coloring yellow changed cells in yesterday and in today the cells that were different in yesterday
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 22/11/2017 by bob
    '
    '
    Windows("todaydata.xls").Activate
    Range("A1:k13").Select
    Selection.Copy
    Windows("yesterdaydata.xls").Activate
    Range("AA1:AK13").Select
    ActiveSheet.Paste puts a temporary copy of todaydata into the yesterday spreadsheet
    For j = 2 To 13 look at each row of the today data
    For z = 2 To 11 and compare with yesterday data [to see if there is a matching key]
    If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15 if a match found then compare all cells in that row with the today row cells
    15 Next z if no match try next row in yesterday
    GoTo 80 if no match is found then look at the next row in the today data table
    20 For y = 2 To 11
    If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
    30 Cells(z, y) = Cells(j, y + 26) updates yesterday cell
    Cells(z, y).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With colors yesterday cell
    Cells(j, y + 26).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With colors corresponding cell in temporary today data table
    40 Next y
    80 Next j
    Range("aa1:ak13").Select
    Selection.Copy
    Windows("todaydata.xls").Activate
    Range("a1:k13").Select
    ActiveSheet.Paste puts the amended temporary today data table back into today spreadsheet
    Windows("yesterdaydata.xls").Activate
    Range("AA1:AK13").Select
    Selection.ClearContents
    Selection.Interior.ColorIndex = xlNone deletes the temporary today table from the yesterday spreadsheet
    Cells(1, 1).Select
    999 End Sub

  2. #32
    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

    Welcome back Bob and thanks for sticking with the project as it's not easy I know, especially now that we've reached Page 4 For clarity (I hope) here's a recap:

    (A) In post 15 I split the requirement into 2 sub-issues - Issue#1 was more complex as layouts were different; Issue#2 involves identical layouts (detail and sample in post#18).

    (B) your new macro seems to be trying to solve across both issues and looks good but we need to take it one step at a time as below i.e. resolve Issue#2 before getting back to the initial post (aka 'Issue#1')

    So to finalise Issue#2 (simple compare):

    - no need to update anything, just highlight in each the records that don't match
    - copy the non-matches in todaydata to a new sheet in the the todaydata workbook
    - replace "Windows("todaydata.xls").Activate" with a full address as I don't have access to Windows directory ... previous macro bombed due to that (I think!) - try P:\Downloads instead? (I would try but not sure of syntax rules)

    I think your macro covers most of the above - just needs some code removed and the address issue resolved (only refer to the previous posts referred to if you're completely stuck!!)

    If we get that done and tested ok I can advise what is to happen to the records extracted to the new worksheet i.e. Issue#1 (will involve compare with another workbook with diff layout & updating cells where a match is found)

    Here's hoping that's understandable - thanks again!!

  3. #33
    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

    - copy the non-matches in todaydata to a new sheet in the the todaydata workbook

    do you mean just the individual cells ie key 102 date1 ?

    cull address do you mean something like f:\bob\todaydata.xls ?

  4. #34
    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
    - copy the non-matches in todaydata to a new sheet in the the todaydata workbook

    do you mean just the individual cells ie key 102 date1 ?

    cull address do you mean something like f:\bob\todaydata.xls ?
    I) copy the full row
    ii) yes - use P:\Downloads\todaydata.xlsm ..... probably best to use .xlsm for both workbooks to ensure macro-enabled (for future if not all reqd now)

    Good luck with that and thank you!

  5. #35
    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

    as I don't have access to Windows directory

    does this mean your company does not use windows - or that it is blocked to you ?

  6. #36
    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

    My company uses Windows alright but we are not allowed access C:\Windows directory. Can we try the code for P:\Windows\**filename.xlsm** as above and see if that works - should do. I'm against the clock on this now Bob so appreciate closure this week if at all possible. If we don't succeed, at least we'll have given it a good shot i.e. you mostly!!

  7. #37
    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

    If you open both workbooks and run this macro code as part of the macro we have rather than the windows refs, it works....

    Sub Macro7()
    '
    ' Macro7 Macro
    ' Macro recorded 06/12/2017 by bob
    '


    '
    ActiveWindow.ActivateNext
    End Sub

  8. #38
    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
    If you open both workbooks and run this macro code as part of the macro we have rather than the windows refs, it works....

    Sub Macro7()
    '
    ' Macro7 Macro
    ' Macro recorded 06/12/2017 by bob
    '


    '
    ActiveWindow.ActivateNext
    End Sub
    I assume you mean to amend your macro from Post#31? I'm not clear where exactly so could you post the full amended macro please and I will test it asap?

    Just to be clear, the macro needs to take account of the changes requested in my Post#32 as follows:

    So to finalise Issue#2 (simple compare):

    - no need to update anything, just highlight in each the records that don't match
    - copy the non-matches in todaydata to a new sheet in the the todaydata workbook
    - replace "Windows("todaydata.xls").Activate" with a full address as I don't have access to Windows directory - try P:\Downloads instead?

  9. #39
    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

    Con Number (key) Tr Code C Start Date Fin Date Approved Value Used Value Ven Numb Ven Name BU REF
    101 NNN ZZZ NNN PPP A A A A A A
    102 A A YYY A A A A A A A
    201 A A A A A A A A A A
    202 A A A A A A A A A A
    103 A A XXX VVV A A A A A A
    203 A A A A A A A A A A
    204 A A A A A A A A A A
    108 A A WWW TTT SSS RRR A A A A
    205 A A A A A A A A A A
    206 A A A A A A A A A A
    207 A A A A A A A A A A
    110 A A A A A A A A A A
    just to make sure we are in agreement before I carry on
    this macro now JUST highlights cells in today and yesterday if they differ
    no cell has been updated
    is a non match defined as a row with no cell highlighted ?
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 22/11/2017 by bob
    '
    '
    Range("A1:k13").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Range("AA1:AK13").Select
    ActiveSheet.Paste
    For j = 2 To 13
    For z = 2 To 11
    If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15
    15 Next z
    GoTo 80
    20 For y = 2 To 11
    If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
    30 Cells(z, y).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Cells(j, y + 26).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    40 Next y
    80 Next j
    Range("AA1:AK13").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Cells(1, 1).Select
    ActiveSheet.Paste
    ActiveWindow.ActivateNext
    Range("AA1:AK13").Select
    Selection.ClearContents
    999 End Sub

  10. #40
    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

      
    Con Number (key) Tr Code C Start Date Fin Date Approved Value Used Value Ven Numb Ven Name BU REF
    101 A A A A A A A A A A 1
    102 A A A A A A A A A A 1
    103 A A A A A A A A A A 1
    104 A A A A A A A A A A
    105 A A A A A A A A A A
    106 A A A A A A A A A A
    107 A A A A A A A A A A
    108 A A A A A A A A A A 1
    109 A A A A A A A A A A
    110 A A A A A A A A A A
    above table is yesterday.xls
    any non equal cells in today and yesterday are coloured yellow
    the NEW last stage is to (with today table) put only rows with at least one non equal cell into sheet2 of today
    I confirm that sheet2 of today workbook has only the rows where at least one discrepancy is showing
    note both workbooks must be open before you run the macro
    THE TABLE BELOW IS TODAYDATA.XLS
    Con Number (key) Tr Code C Start Date Fin Date Approved Value Used Value Ven Numb Ven Name BU REF
    101 NNN ZZZ NNN PPP A A A A A A
    102 A A YYY A A A A A A A
    201 A A A A A A A A A A
    202 A A A A A A A A A A
    103 A A XXX VVV A A A A A A
    203 A A A A A A A A A A
    204 A A A A A A A A A A
    108 A A WWW TTT SSS RRR A A A A
    205 A A A A A A A A A A
    206 A A A A A A A A A A
    207 A A A A A A A A A A
    110 A A A A A A A A A A
    latest macro
    Sub Macro4()
    '
    ' Macro4 Macro
    ' Macro recorded 22/11/2017 by bob
    '
    '
    Range("A1:k13").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Range("AA1:AK13").Select
    ActiveSheet.Paste
    For j = 2 To 13
    For z = 2 To 11
    If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15
    15 Next z
    GoTo 80
    20 For y = 2 To 11
    If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
    30 Cells(z, y).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Cells(j, y + 26).Select
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Cells(z, 12) = 1
    Cells(j, 38) = 1
    40 Next y
    80 Next j
    Range("AA1:AL13").Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Cells(1, 1).Select
    ActiveSheet.Paste
    ActiveWindow.ActivateNext
    Range("AA1:AL13").Select
    Selection.ClearContents
    ActiveWindow.ActivateNext
    For myrow = 2 To 13
    If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
    100 Rows(myrow).Select
    Selection.Copy
    Sheets(2).Select
    Rows(Cells(1, 17)).Select
    ActiveSheet.Paste
    Sheets(1).Select
    200 Next myrow
    Sheets(1).Select
    999 End Sub

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