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.
(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:
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!
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Period | Appt Date | Acct | ||
2 | 7:30a | 09/20/21 | 891543 | ||
3 | 8:00a | 09/20/21 | 889656 | ||
4 | 8:20a | 09/20/21 | 840965 | ||
5 | 8:40a | 09/20/21 | 882645 | ||
6 | 9:00a | 09/20/21 | 886328 | ||
7 | 10:00a | 09/20/21 | 889833 | ||
8 | 10:20a | 09/20/21 | 863003 | ||
9 | 10:40a | 09/20/21 | 866761 | ||
10 | 11:00a | 09/20/21 | 779648 | ||
11 | 11:20a | 09/20/21 | 783108 | ||
12 | 12:30p | 09/20/21 | 768720 | ||
13 | 1:00p | 09/20/21 | 821251 | ||
14 | 1:15p | 09/20/21 | 826809 | ||
15 | 1:30p | 09/20/21 | 831074 | ||
16 | 1:45p | 09/20/21 | 882543 | ||
17 | 2:15p | 09/20/21 | 866659 | ||
18 | 2:45p | 09/20/21 | 848271 | ||
19 | 3:00p | 09/20/21 | 705428 | ||
20 | 7:30a | 09/21/21 | 800510 | ||
21 | 7:45a | 09/21/21 | 881708 | ||
22 | 8:15a | 09/21/21 | 811151 | ||
23 | 8:30a | 09/21/21 | 774925 | ||
24 | 8:45a | 09/21/21 | 772766 | ||
25 | 9:15a | 09/21/21 | 889670 | ||
26 | 9:30a | 09/21/21 | 889542 | ||
27 | 9:45a | 09/21/21 | 853676 | ||
28 | 10:00a | 09/21/21 | 885454 | ||
29 | 10:15a | 09/21/21 | 852353 | ||
30 | 10:30a | 09/21/21 | 747180 | ||
31 | 10:45a | 09/21/21 | 852974 | ||
32 | 12:30p | 09/21/21 | 839178 | ||
33 | 12:45p | 09/21/21 | 891024 | ||
34 | 1:00p | 09/21/21 | 891143 | ||
35 | 1:15p | 09/21/21 | 860808 | ||
36 | 1:30p | 09/21/21 | 820217 | ||
37 | 1:45p | 09/21/21 | 885758 | ||
38 | 2:15p | 09/21/21 | 889630 | ||
39 | 2:45p | 09/21/21 | 879850 | ||
40 | |||||
41 | |||||
42 | |||||
43 | |||||
44 | |||||
45 | |||||
Old |
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Period | Appt Date | Acct | ||
2 | 7:30a | 09/20/21 | 891543 | ||
3 | 8:00a | 09/20/21 | 889656 | ||
4 | 8:20a | 09/20/21 | 840965 | ||
5 | 8:40a | 09/20/21 | 882645 | ||
6 | 9:00a | 09/20/21 | 886328 | ||
7 | 10:00a | 09/20/21 | 889833 | ||
8 | 10:20a | 09/20/21 | 863003 | ||
9 | 10:40a | 09/20/21 | 123838 | ||
10 | 11:00a | 09/20/21 | 779648 | ||
11 | 11:20a | 09/20/21 | 783108 | ||
12 | 12:30p | 09/20/21 | 768720 | ||
13 | 1:00p | 09/20/21 | 821251 | ||
14 | 1:15p | 09/20/21 | 826809 | ||
15 | 1:30p | 09/20/21 | 831074 | ||
16 | 1:45p | 09/20/21 | 882543 | ||
17 | 2:15p | 09/20/21 | 866659 | ||
18 | 2:45p | 09/20/21 | 848271 | ||
19 | 3:00p | 09/20/21 | 705428 | ||
20 | 7:30a | 09/21/21 | 800510 | ||
21 | 7:45a | 09/21/21 | 881708 | ||
22 | 8:15a | 09/21/21 | 811151 | ||
23 | 8:30a | 09/21/21 | 774925 | ||
24 | 8:45a | 09/21/21 | 772766 | ||
25 | 9:00a | 09/21/21 | 863857 | ||
26 | 9:15a | 09/21/21 | 889670 | ||
27 | 9:30a | 09/21/21 | 889542 | ||
28 | 9:45a | 09/21/21 | 853676 | ||
29 | 10:00a | 09/21/21 | 885454 | ||
30 | 10:15a | 09/21/21 | 852353 | ||
31 | 10:30a | 09/21/21 | 885758 | ||
32 | 10:45a | 09/21/21 | 852974 | ||
33 | 11:00a | 09/21/21 | 760607 | ||
34 | 12:30p | 09/21/21 | 839178 | ||
35 | 12:45p | 09/21/21 | 891024 | ||
36 | 1:00p | 09/21/21 | 891143 | ||
37 | 1:15p | 09/21/21 | 333564 | ||
38 | 1:30p | 09/21/21 | 820217 | ||
39 | 1:45p | 09/21/21 | 221351 | ||
40 | 2:15p | 09/21/21 | 889630 | ||
41 | 2:45p | 09/21/21 | 879850 | ||
42 | |||||
43 | |||||
44 | |||||
45 | |||||
New |
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Period | Appt Date | Acct | ||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
6 | |||||
7 | |||||
8 | |||||
9 | 10:40a | 09/20/21 | 866761 | ||
10 | |||||
11 | |||||
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | |||||
17 | |||||
18 | |||||
19 | |||||
20 | |||||
21 | |||||
22 | |||||
23 | |||||
24 | |||||
25 | |||||
26 | |||||
27 | |||||
28 | |||||
29 | |||||
30 | 10:30a | 09/21/21 | 747180 | ||
31 | |||||
32 | |||||
33 | |||||
34 | |||||
35 | 1:15p | 09/21/21 | 860808 | ||
36 | |||||
37 | 1:45p | 09/21/21 | 885758 | ||
38 | |||||
39 | |||||
40 | 0 | 0 | 0 | ||
41 | 0 | 0 | 0 | ||
42 | 0 | 0 | 0 | ||
43 | 0 | 0 | 0 | ||
44 | 0 | 0 | 0 | ||
45 | 0 | 0 | 0 | ||
Removed |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:C45 | A2 | =IF(COUNTIFS(New!$C:$C, Old!$C2, New!$B:$B,Old!$B2,New!$A:$A,Old!$A2)<1, Old!A2, "") |
Book1 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Period | Appt Date | Acct | ||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
6 | |||||
7 | |||||
8 | |||||
9 | 10:40a | 09/20/21 | 123838 | ||
10 | |||||
11 | |||||
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | |||||
17 | |||||
18 | |||||
19 | |||||
20 | |||||
21 | |||||
22 | |||||
23 | |||||
24 | |||||
25 | 9:00a | 09/21/21 | 863857 | ||
26 | |||||
27 | |||||
28 | |||||
29 | |||||
30 | |||||
31 | 10:30a | 09/21/21 | 885758 | ||
32 | |||||
33 | 11:00a | 09/21/21 | 760607 | ||
34 | |||||
35 | |||||
36 | |||||
37 | 1:15p | 09/21/21 | 333564 | ||
38 | |||||
39 | 1:45p | 09/21/21 | 221351 | ||
40 | |||||
41 | |||||
42 | 0 | 0 | 0 | ||
43 | 0 | 0 | 0 | ||
44 | 0 | 0 | 0 | ||
45 | 0 | 0 | 0 | ||
Added |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A2:C45 | A2 | =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!