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

R

ramkrau

New Member
Joined
Jan 9, 2019
Messages
14
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Barcode[/TD]
[TD]Value[/TD]
[TD]Type[/TD]
[TD]Location[/TD]
[TD]Attribute A[/TD]
[TD]Attribute B[/TD]
[/TR]
[TR]
[TD]Widget[/TD]
[TD]123[/TD]
[TD]high[/TD]
[TD]soft[/TD]
[TD]top[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Gidget[/TD]
[TD]254[/TD]
[TD]low[/TD]
[TD]hard[/TD]
[TD]top[/TD]
[TD]green[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Thing[/TD]
[TD]555[/TD]
[TD]mid[/TD]
[TD]mix[/TD]
[TD]bottom[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Stuff[/TD]
[TD]635[/TD]
[TD]low[/TD]
[TD]mix[/TD]
[TD]bottom[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2("New") - I will input this data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Barcode[/TD]
[TD]Value[/TD]
[TD]Type[/TD]
[TD]Location[/TD]
[TD]Attribute A[/TD]
[TD]Attribute B[/TD]
[/TR]
[TR]
[TD]Gidgets[/TD]
[TD]254[/TD]
[TD]low[/TD]
[TD]soft[/TD]
[TD]top[/TD]
[TD]aqua[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Thing[/TD]
[TD]555[/TD]
[TD]mid[/TD]
[TD]mix[/TD]
[TD]bottom[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]StuffAndThing[/TD]
[TD]635[/TD]
[TD]lower[/TD]
[TD]mixed[/TD]
[TD]left[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]DooDad[/TD]
[TD]632[/TD]
[TD]low[/TD]
[TD]lower[/TD]
[TD]mix[/TD]
[TD]yellow[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Widget[/TD]
[TD]123[/TD]
[TD]high[/TD]
[TD]soft[/TD]
[TD]top[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
</tbody>[/TABLE]

Sheet3("Final") - Macro will create this data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Barcode[/TD]
[TD]Value[/TD]
[TD]Type[/TD]
[TD]Location[/TD]
[TD]Attribute A[/TD]
[TD]Attribute B[/TD]
[/TR]
[TR]
[TD]Gidgets[/TD]
[TD]254[/TD]
[TD]low[/TD]
[TD]soft[/TD]
[TD]top[/TD]
[TD]aqua[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Thing[/TD]
[TD]555[/TD]
[TD]mid[/TD]
[TD]mix[/TD]
[TD]bottom[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]StuffAndThing[/TD]
[TD]635[/TD]
[TD]lower[/TD]
[TD]mixed[/TD]
[TD]left[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]DooDad[/TD]
[TD]632[/TD]
[TD]low[/TD]
[TD]lower[/TD]
[TD]mix[/TD]
[TD]yellow[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Widget[/TD]
[TD]123[/TD]
[TD]high[/TD]
[TD]soft[/TD]
[TD]top[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
</tbody>[/TABLE]

Sheet4("Report") - Macro will create this data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name Changes[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Value Changes[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Type Changes[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Location Changes[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Attribute A Changes[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Attribute B Changes[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]New Products[/TD]
[TD]1

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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:

Code: 
[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]
 
Upvote 0
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?
 
Upvote 0
ramkrau said:
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.
Click to expand...

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.
Click to expand...
Ah, you're right, the 2 sheets are flipped.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
198
frewert
F
A
  • Question Question
Need VBA code to copy/paste data from one sheet to another with multiple criteria and complex structure
Replies
0
Views
2K
Amir Wisal
A
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
665
fjns
F
G
  • Question Question
DESPERATELY NEED HELP: How to delete an old graph and replace with a new one [VBA Macro]
Replies
1
Views
2K
Jon Peltier
J
L
  • Question Question
Comparing cells of text in two different sheets to find and highlight differences/updates
Replies
1
Views
2K
lorikgator
L

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

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