Can you customise the results from comparing two worksheets?

[Trout58]

Hi All

Spent ages on the web testing lots of different options for comparing two worksheets in the same workbook using formulas and conditional formatting but they either don’t work at all or don’t return the results how I would ideally like them to. I know exactly what I want but only have basic skills or knowledge so don’t know how to get it or, maybe it can’t be achieved.

I hope those with far more experience will let me know if I’m dreaming or not lol!

I have the capacity to run and export a real time Excel membership list form a parent body whenever I want to. These reports comprise 15 columns with one row per member and up to 25,000 rows (members).
1st data cell is B5 – end data cell is P25000

As a rule, there is always a variance between reports in respect to the number of members on each report for varying reasons, most of which are legitimate. However, with that volume of records it’s simply not practical to manually look for which members may have dropped off (or been added) to the membership list between runs so ‘ideally’ I would like a formula / macro that would do the following:

The workbook has three sheets: 1_Previous, 2_Current, 3_Comparison,
a. I would like a formula / macro that will compare 1 against 2 and enter the results in 3 in the following way;
b. So, if Mary Smith’s record appears in 1 but not in 2, then enter her entire record row in 3;
c. Enter all such records in 3 with the same background (conditional formatting) colour;
d. If, Bob Brown doesn’t appear in 1 but does appear in 2, then enter his entire record row in 3;
e. Enter all such records in 3 with a different background (conditional formatting) colour; and
f. All records to appear in 3 in alphabetical order by member Surname, which is column C.

I don’t want the comparison on the basis of rows only i.e. if Mary Smith's record appears in say row 18791 in 1 but drops of and doesn’t appear in 2; it is of no importance to me to know whose record now appears in row 18791 in 2. But, it is essential that Mary Smith's record appears in 3 with the allocated background colour that immediately tells me she was in 1 but is not in 2.

Have attached a sample workbook that shows exactly what i would like to happen in a perfect world - maybe it can't - appreciate any help on this

Cheers19914

[SamT]

I would put new members, Bob brown, in a fourth sheet, instead of formatting him differently on the same sheet as the dropped members, Mary Smith.

I would add The records on sheet Previous to a dictionary, with Student ID as the Dictionary Key
Repeat for the Current Members.

Search results for "Scripting Runtime Dictionary": https://duckduckgo.com/?q=Scripting+runtime+dictionary&atb=v19&ia=web

Dictionary Keys are Arrays so you can quickly compare the two arrays to one another with simple loops

This is just a suggestion, not full and complete Code. Heh! Even the logic may be backwards. Hopefully it gives you enough of a idea to write the code you need


CurrentKeys = CurrentDict.Keys
PreviousKeys = PreviousDict.Keys

'First a loop to find Dropped members
For i = LBound(CurrentKeys) to UBound(CurrentKeys)
For j = LBound(PreviousKeys) to UBound(PreviousKeys)
If PreviousKeys(j) = CurrentKeys(i) then
FoundCurrent = True
Exit Loop
End If
Next j

If Not FoundCurrent Then
Sheets("Dropped").Cells(Rows.Count, "B").Offset(1).Resize(1, 15) = CurrentDict(CurrentKeys(j))
FoundCurrent = False
Next i

'Then we invert the loop to find new members
For i = LBound(PreviousKeys) to UBound(PreviousKeys)
For j = LBound(CurrentKeys) to UBound(CurrentKeys)
If CurrentKeys(i) = PreviousKeys(j) then
FoundPrevious = True
Exit Loop
End If
Next j

If Not FoundPrevious Then
Sheets("NewMembers").Cells(Rows.Count, "B").Offset(1).Resize(1, 15) = PreviousDict(PreviousKeys(j))
FoundPrevious = False
Next i


You can write even faster and more consise code if you use ObectExists instead of looping thru the keys. :devil2: