Show differences (removed, rescheduled, added), entire rows

arcylix

New Member
Joined
Sep 12, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have the following four sample sheets (well, there's a fifth and sixth one, but they aren't needed for the purposes of this post). These sheets list 3 columns, but in reality, there's 16 columns total.

Book1
ABC
1PeriodAppt DateAcct
2 7:30a09/20/21891543
3 8:00a09/20/21889656
4 8:20a09/20/21840965
5 8:40a09/20/21882645
6 9:00a09/20/21886328
710:00a09/20/21889833
810:20a09/20/21863003
910:40a09/20/21866761
1011:00a09/20/21779648
1111:20a09/20/21783108
1212:30p09/20/21768720
13 1:00p09/20/21821251
14 1:15p09/20/21826809
15 1:30p09/20/21831074
16 1:45p09/20/21882543
17 2:15p09/20/21866659
18 2:45p09/20/21848271
19 3:00p09/20/21705428
20 7:30a09/21/21800510
21 7:45a09/21/21881708
22 8:15a09/21/21811151
23 8:30a09/21/21774925
24 8:45a09/21/21772766
25 9:15a09/21/21889670
26 9:30a09/21/21889542
27 9:45a09/21/21853676
2810:00a09/21/21885454
2910:15a09/21/21852353
3010:30a09/21/21747180
3110:45a09/21/21852974
3212:30p09/21/21839178
3312:45p09/21/21891024
34 1:00p09/21/21891143
35 1:15p09/21/21860808
36 1:30p09/21/21820217
37 1:45p09/21/21885758
38 2:15p09/21/21889630
39 2:45p09/21/21879850
40
41
42
43
44
45
Old

Book1
ABC
1PeriodAppt DateAcct
2 7:30a09/20/21891543
3 8:00a09/20/21889656
4 8:20a09/20/21840965
5 8:40a09/20/21882645
6 9:00a09/20/21886328
710:00a09/20/21889833
810:20a09/20/21863003
910:40a09/20/21123838
1011:00a09/20/21779648
1111:20a09/20/21783108
1212:30p09/20/21768720
13 1:00p09/20/21821251
14 1:15p09/20/21826809
15 1:30p09/20/21831074
16 1:45p09/20/21882543
17 2:15p09/20/21866659
18 2:45p09/20/21848271
19 3:00p09/20/21705428
20 7:30a09/21/21800510
21 7:45a09/21/21881708
22 8:15a09/21/21811151
23 8:30a09/21/21774925
24 8:45a09/21/21772766
25 9:00a09/21/21863857
26 9:15a09/21/21889670
27 9:30a09/21/21889542
28 9:45a09/21/21853676
2910:00a09/21/21885454
3010:15a09/21/21852353
3110:30a09/21/21885758
3210:45a09/21/21852974
3311:00a09/21/21760607
3412:30p09/21/21839178
3512:45p09/21/21891024
36 1:00p09/21/21891143
37 1:15p09/21/21333564
38 1:30p09/21/21820217
39 1:45p09/21/21221351
40 2:15p09/21/21889630
41 2:45p09/21/21879850
42
43
44
45
New

Cell Formulas
RangeFormula
A2:C45A2=IF(COUNTIFS(New!$C:$C, Old!$C2, New!$B:$B,Old!$B2,New!$A:$A,Old!$A2)<1, Old!A2, "")

Cell Formulas
RangeFormula
A2:C45A2=IF(COUNTIFS(Old!$C:$C, New!$C2, Old!$B:$B, New!$B2, Old!$A:$A, New!$A2)<1, New!A2, "")


(If there's a way to put multiple sheets in one minisheet, I do not know how to pull that off, so I apologize in advance).

One of the sheets mentioned above that isn't listed is a "Tracker" sheet, where I keep track of notes and statuses. I originally started it by copying all of 'New', and now I manually add/remove from it dependent upon a daily-run report. Every morning, I start the day off by copying 'New' to 'Old', and clearing out 'New'. I generate the day's report and copy that to 'New'. I run calculate, and I go to 'Removed'/'Added' to see which appointments have been removed and which have been added, respectively. In other words, if the time, date, and account number on the 'Old' sheet is not found on the 'New' sheet, then that means that appointment was removed, and if it was found on the 'New' sheet but not the 'Old', then that appointment was added. This works a majority of the time. However, that's why there's the last sheet, called 'Compare'. In that sheet, I index the account numbers on the 'Tracker' sheet in Column A, and index the account numbers from the 'New' sheet in Column C, then run a match formula in column E. As long as everything matches, then I know nothing was missed. But if something fails to match, then I have to manually find it and add or remove it appropriately.

I've found a few VBA scripts that will detect differences between sheets, but I need to be able to view the entire row, not just one value. The best one I've found so far is this:

VBA Code:
Sub FindMissing()

    Dim i As Long, mm As Long
    Dim valsM As Variant, valsQ As Variant, valsMM As Variant

    With Worksheets("New")
        valsM = .Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp)).Value2
    End With

    With Worksheets("Old")
        valsQ = .Range(.Cells(1, "C"), .Cells(.Rows.Count, "C").End(xlUp)).Value2
    End With

    ReDim valsMM(1 To (UBound(valsM, 1) + UBound(valsQ, 1)), 1 To 2)
    mm = 1
    valsMM(mm, 1) = "value"
    valsMM(mm, 2) = "missing from"

    For i = LBound(valsM, 1) To UBound(valsM, 1)
        If IsError(Application.Match(valsM(i, 1), valsQ, 0)) Then
            mm = mm + 1
            valsMM(mm, 1) = valsM(i, 1)
            valsMM(mm, 2) = "old"
        End If
    Next i

    For i = LBound(valsQ, 1) To UBound(valsQ, 1)
        If IsError(Application.Match(valsQ(i, 1), valsM, 0)) Then
            mm = mm + 1
            valsMM(mm, 1) = valsQ(i, 1)
            valsMM(mm, 2) = "new"
        End If
    Next i

    valsMM = helperResizeArray(valsMM, mm)

    With Worksheets("Test")
        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Resize(UBound(valsMM, 1), UBound(valsMM, 2)) = valsMM
        End With
    End With

End Sub

Function helperResizeArray(vals As Variant, x As Long)
    Dim arr As Variant, i As Long

    ReDim arr(1 To x, 1 To 2)

    For i = LBound(arr, 1) To UBound(arr, 1)
        arr(i, 1) = vals(i, 1)
        arr(i, 2) = vals(i, 2)
    Next i

    helperResizeArray = arr
End Function

It seems to work reliably, but instead of two columns of 'value' and 'missing from', I just want the entire missing row to be pasted, preferably in its respective slots. How can I modify the above code to do what I want? Or can someone provide an alternative code that suits my purpose?

Bonus: It would be great if I could add a 'Rescheduled' sheet for those that are on both 'Old' and 'New' but the days/times are different. It's not absolutely required, just a quality-of-life enhancement for me. Thanks in advance. Here's to hoping this post doesn't bomb as badly as my last one did! I'm still quite new to the ways of VBA, and I have a lot to learn, but I'm willing to learn!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top