Data Comparison Check - Updating Cases

[Nairobi Nice]

Hi there

The issue I have at hand is 2 fold, I need to check for missing data in the attached file, using the SKU code, as well as the “Brand_Price” column.
The data in the 2 tabs is flipped (Thanks Mike7952), meaning one record can appear in multiple rows, I have highlighted one example (see attached sample file).

You will note that in ‘Data 2’ for the record ‘956711’, there are some values under Brand price that are reading “0” while in ‘Data 1’ they have values – This basically means someone did not update that SKU Code.

I would like to have a macro that checks in “Data 1” under the “SKU_code” tied in to the “Outlet Code” and returns results for cases with a null value i.e. if “Data 2” has a null value for SKU code for that particular Outlet code (956711) while in “Data 1” it has a value, then the cells should be highlighted.

The second bit, which is secondary really, I would like in column M, under “Total Stock”, if this value in “Data 2” is greater than “Data 1” by 50% or less than “Data 1” by 50%, then this should also be highlighted.

Many Thanks

[cjo]

Hi,
here's code for the first bit:

Please Login or Register  to view this content.

However, I have a question regarding the second bit. On 'Data 1', rows 55 and 56 are duplicated values in columns A and D. How do you want this handled? Do you want the sum of the duplicated values, or some other methodology?

[Nairobi Nice]

Thanks Cjo, that shoudn't be, somethings wrong with my data, one can only have one SKU code per outlet, will look into it. Let me try what you have here and will get back to you, thanks

[Nairobi Nice]

The check on the data is on column M, 'Total_Stock', please assume no duplicates.
Total Stock in 'Data 1' should not be greater or lower than 50% of the value in 'Data 2'
The other script works fine, Thanks

[cjo]

How do you want highlighting to happen if BOTH criteria happen? Yellow for the first, Red for the second, Orange for both?

[Nairobi Nice]

Prefer the different colours please, so quickly I can filter with colour for corrective checks, thanks

[cjo]

I made failing the first criteria yellow, failing the second criteria red, and failing both Blue

Please Login or Register  to view this content.

[Nairobi Nice]

Many Thanks CJO.

Cosmetic changes please, I am bespectacled and those fill in colours are rather 'strong' for me, grey and brown maybe, thanks

[cjo]

So we don't go back and forth, currently I'm using
Yellow
Red
Blue

Which colors do you want these replaced with?
Yellow - Yellow
Red-Brown
Blue - grey
?

[Nairobi Nice]

The below will do

Yellow - green
Red - brown
Blue - grey

Thanks

[cjo]

try this:

Please Login or Register  to view this content.

[Nairobi Nice]

Thanks for this, but am having some hiccups along the way, when I run the macro, I see action on only the first 20 rows, I checked row 29 and and for the Total stock, Data 1 is 27 and Data 2 is 10, an over 50% difference yet there is no highlight.

Less important - can you limit the highlights to cells instead of the whole row? i.e. for the first check on Brand_Price could the highlights for the cases be limited to that column?

[cjo]

try this

Please Login or Register  to view this content.

[Nairobi Nice]

Thanks Cjo

Apologies for late response, went on an abrupt safari, could not log in

Will get back to you

[Nairobi Nice]

Not running

Gets stuck at ln 27 - Debug.Assert i <> 29

[Nairobi Nice]

I have deleted the line for now and it ran full course. There are some changes in the new run that have dropped from what you had provided earlier i.e. when the record does not completely exist on the second data set, was turning green / yellow - thats not happening.

Also it seems combining criteria to give one result i not simplifying the work, as then one has to configure wihich of the 2 criteria to one needs to act. If this could happen

Criteria 1 - green - The record in set 1 does not completely exist in set 2
Criteria 2 - grey - Record exists in set 2 but has a null value
Criteria 3 - Brown - The Total Stock in set 1 should not be lower or greater by 50% of set 2
Criteria 4 - No Fill - The record has no issues.

Many thanks

[cjo]

You can remove that line: i accidentally left it in while I debugged.