MACRO HELP NEEDED: Complex comparison, text coloring, and reporting between an old and new sheet.

[ramkrau]

I have a complex macro in my mind that I want to build, but such little macro experience that I just can't build it alone.

One workbook.
Three sheets.
Several hundred rows, but only columns A through G.
Sheet1 "Old"
Sheet 2 "New"
Sheet 3 "Final"
Sheet 4 "Report"
Each workbook has the exact same headers across Columns A through G

Each row is a unique product. Column B is the barcode that will never change for the product, but everything else could change.
The rows could all be out of order between Old and New.

I need a Macro that will do several things.

Compare Old and New based on Column B, the barcode.
If there was no change in Column A of that same row, then display from Old.
If there was a change in Column A, display the New and make the text orange.

Compare Old and New based on Column B, the barcode.
If there was no change in Column C-G, then display from Old.
If there was a change in Column C, Column D, Column E, column F, or Column G, then display new and change text color to Green.

Compare Old and New based on Column B, the barcode.
If there are any values in New that didn't exist in Old, then copy from New and make the text in columns A through G Blue.

The end result should then be identical to New, but with values that were changed displaying in certain colors, and values that are new displaying in other colors. (The order of the rows does matter and needs to be the same in my result as in the New.)

Then, on Sheet4 ("Report"), I need a comparison of the changes between old and new with the following info:

Changes from Old to New in Column A, excluding brand new entries.
Changes from Old to New in Column C, excluding brand new entries.
Changes from Old to New in Column D, excluding brand new entries.
Changes from Old to New in Column E, excluding brand new entries.
Changes from Old to New in Column F, excluding brand new entries.
Changes from Old to New in Column G, excluding brand new entries.

Then finally, how many brand new entries are there in the New.

Those results could either be a dialogue box pop-up, or could produce on a separate worksheet. I just need to log them for another external system.

For me, this seems like a HUGE ask, but maybe it's child's play for some other folks... if someone knows how to make this happen I'd be so super appreciative! Right now, I have to do this manually, several times a day, and it takes so much of my time to manually compare things, filter the correct results, apply the color coding, etc. This would save me hours upon hours. Thank you in advance!!!



Samples of the kind of data I'll have are below, in case my explanations were confusing...

Sheet1("Old") - I will input this data.

Name	Barcode	Value	Type	Location	Attribute A	Attribute B
Widget	123	high	soft	top	blue	green
Gidget	254	low	hard	top	green	green
Thing	555	mid	mix	bottom	blue	green
Stuff	635	low	mix	bottom	yellow	blue

<tbody>
</tbody>

Sheet2("New") - I will input this data.

Name	Barcode	Value	Type	Location	Attribute A	Attribute B
Gidgets	254	low	soft	top	aqua	green
Thing	555	mid	mix	bottom	blue	green
StuffAndThing	635	lower	mixed	left	yellow	blue
DooDad	632	low	lower	mix	yellow	yellow
Widget	123	high	soft	top	blue	green

<tbody>
</tbody>

Sheet3("Final") - Macro will create this data.

Name	Barcode	Value	Type	Location	Attribute A	Attribute B
Gidgets	254	low	soft	top	aqua	green
Thing	555	mid	mix	bottom	blue	green
StuffAndThing	635	lower	mixed	left	yellow	blue
DooDad	632	low	lower	mix	yellow	yellow
Widget	123	high	soft	top	blue	green

<tbody>
</tbody>

Sheet4("Report") - Macro will create this data.

Name Changes	2
Value Changes	1
Type Changes	2
Location Changes	1
Attribute A Changes	1
Attribute B Changes	0
New Products	1

<tbody>
</tbody>

 

[rlv01]

That seems like a lot of work.

https://www.mrexcel.com/consulting-services/

 

[Akuini]

Try this:

You manually:
- copy the data (including header) from sheet New to Report, the header must be at row 1
- create a new sheet, name it "Final"

then run this macro:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1084309a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1084309-macro-help-needed-complex-comparison-text-coloring-reporting-between-old-new-sheet.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], a [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR], e [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Old"[/COLOR])
va = .Range([COLOR=brown]"A1:G"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

Sheets([COLOR=brown]"Report"[/COLOR]).Activate
[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare

    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        d(va(i, [COLOR=crimson]2[/COLOR])) = i
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]Set[/COLOR] e = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
e.CompareMode = vbTextCompare

[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]7[/COLOR], [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])

    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]7[/COLOR]
        e(i) = [COLOR=crimson]0[/COLOR]
        vc(i, [COLOR=crimson]1[/COLOR]) = Cells([COLOR=crimson]1[/COLOR], i)
    [COLOR=Royalblue]Next[/COLOR]

vb = Range([COLOR=brown]"A1:G"[/COLOR] & Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR])
    x = vb(i, [COLOR=crimson]2[/COLOR])
    [COLOR=Royalblue]If[/COLOR] d.Exists(x) [COLOR=Royalblue]Then[/COLOR]
        flag = [COLOR=Royalblue]False[/COLOR]
        [COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]3[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]7[/COLOR]
                [COLOR=Royalblue]If[/COLOR] Trim(vb(i, j)) <> Trim(va(d(x), j)) [COLOR=Royalblue]Then[/COLOR]
                    Cells(i, j).Font.Color = [COLOR=crimson]26112[/COLOR] [I][COLOR=seagreen]'Green[/COLOR][/I]
                    e(j) = e(j) + [COLOR=crimson]1[/COLOR]
                    flag = [COLOR=Royalblue]True[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]True[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        Cells(i, [COLOR=brown]"A"[/COLOR]).Font.Color = [COLOR=crimson]49407[/COLOR] [I][COLOR=seagreen]'orange[/COLOR][/I]
        a = a + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Else[/COLOR]
        Range([COLOR=brown]"A"[/COLOR] & i & [COLOR=brown]":G"[/COLOR] & i).Font.Color = vbBlue
        b = b + [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Final"[/COLOR])
    .Range([COLOR=brown]"A1:B7"[/COLOR]) = Application.Transpose(Array(e.Keys, e.Items))
    .Range([COLOR=brown]"A1:A7"[/COLOR]) = vc
    .Range([COLOR=brown]"B1"[/COLOR]) = a
    .Range([COLOR=brown]"A8"[/COLOR]) = [COLOR=brown]"New Product"[/COLOR]
    .Range([COLOR=brown]"B8"[/COLOR]) = b
    .Rows([COLOR=crimson]2[/COLOR]).Delete
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]Next[/COLOR]


[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[ramkrau]

This is 99% there - the only issue is that when I plug real information in, the data points for name changes are off and I don't know how to figure that out with the way that you wrote the Macro.

For my REAL data - I should have gotten 94 name changes, but this shows 230.
Also, The Final and Report sheets are flipped, but that's super easy to work around.

Any idea why it may be calculating incorrectly?

 

[Akuini]

This is 99% there - the only issue is that when I plug real information in, the data points for name changes are off and I don't know how to figure that out with the way that you wrote the Macro.

For my REAL data - I should have gotten 94 name changes, but this shows 230.

1. If you count by using the highlight color, is it 94 or 230?
(note: you can filter by the color, then select data in one column, in the status bar you can see "COUNT:")

2. What about the other count (for Value, Type, Location, etc), are they off too?

Also, The Final and Report sheets are flipped, but that's super easy to work around.

Ah, you're right, the 2 sheets are flipped.