Conditional Format - Multiple Criteria and Highlight Corresponding Rows Based on Values

Occasional Visitor

Hi Everyone!


I have a dataset that looks like this:
niquechua_0-1670988344445.png

For data validation purposes I need to conditional formatting to help highlight columns D to U with the following criteria:

Scenario 1: Region Split
1) If [Column A] SKU = SKU (because SKU can have duplicates across multiple rows)

2) And if [Column D] = "Regional split"
Then there must be a corresponding "X" in Column D to I.  

Correct validation:
There should be a corresponding X in each Region per SKU.

niquechua_4-1670988739851.png

 

Incorrect validation Example:
There is no "X" in Column F for AP value so it should be flagged out in Red.

niquechua_1-1670988504452.png

Or if there is an "X" duplicated in Column G for CA value, it should be flagged out in Red.

niquechua_2-1670988579266.png


Or if it is Regional Split and no "X" in Column D to I, flagged out in Red.

niquechua_3-1670988629269.png

 

Scenario 2: Buy month split

1) If [Column A] SKU = SKU (because SKU can have duplicates across multiple rows)

2) And if [Column D] = "Buy Month split"
Then there must be a corresponding "X" in Column J to U.  

Correct validation:
There should be a corresponding X in each Month per SKU.

niquechua_5-1670988819737.png

Incorrect validation Example:
There is no "X" in Column J for JAN value so it should be flagged out in Red.

niquechua_6-1670988851470.png


Or if there is an "X" duplicated in Column K for FEB value, it should be flagged out in Red.

niquechua_7-1670988896390.png

Or if it is Buy Month Split and no "X" in Column J to U, flagged out in Red.

niquechua_8-1670988920033.png

 

I have been cracking my head with this for weeks now and decided to seek help in forum! :'(

If anyone has an idea to share, it would be deeply appreciated! 

Thank you in advance!

1 Reply
There should be a corresponding X in each Region per SKU.

if I were you,I would like convert two dimension table to one dimension first and then test this criteria。

add column Region
to save US EU ...