Forum Discussion

niquechua's avatar
niquechua
Copper Contributor
Dec 14, 2022

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

Hi Everyone!


I have a dataset that looks like this:


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.

 

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



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


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

 

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.



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


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



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

 

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

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    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 ...

Resources