Apr 03 2022 06:31 PM
Apr 03 2022 06:31 PM
I need the row highlighted where 3 conditions are the same:
1. YearWeek = YearWeek (Col C)
2. ProductName = Product Name (Col I)
3. Store = Store (Col A)
Example: Rows 45 & 47 have these criteria met. I'd like that row highlighted.
I have an XLookup to run on rows of data and am stumped as it only gets the first value if these conditions are met and need to highlight and manually sort the rows or come up with a solution. Outside of using "GetPivotData" and summing based on date or creating a sum column using PowerQuery, I don't know what to do. I'm hoping for a conditional formatting solution to highlight and manually sum the information (a ton).
Apr 03 2022 07:48 PMSolution
Row#40 and 41 also meet the criteria, why didn't you highlight them?
Select the range A37:O48 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your requirement.
Apr 03 2022 08:27 PM - edited Apr 03 2022 09:50 PM
1. Select the whole data table starting from Row 37 onwards.
2. Home>Conditional Formatting> New Rule> Use a formula to determine which cells to format.
Under format values where this formula is true, write the formula as below.
Store,YearWeek and ProductName are 3 conditions that you want to match.
All need to be defined names or absolute reference locked with $ sign for both row and column
(for e.g $A$1).
3. Press Format and under fill tab, you may change the background color you want and then press ok.
Apr 03 2022 08:53 PM
Thank you for your support. I appreciate the help.
I didn't highlight the rows because I wanted to provide an example. This workbook has over 19,000 rows of data in it.
I have added the conditional formatting to my spreadsheet. It works perfectly.
Thank you for your response.
I'm a lousy conditional formatting user and appreciate your expertise, very much!
Apr 03 2022 09:34 PM
Apr 03 2022 09:48 PM - edited Apr 03 2022 09:50 PM
Are you referencing the wrong cells?
The formula should be something like that.
3 criterias to meet the conditions are "MADISON", "20 2020" and "Chana Masala bowl".
=AND($A37="MADISON",$C37="20 2020",$I37="Chana Masala bowl")
It should be the right way. But, if it is still not working, I have no idea.
Apr 03 2022 10:06 PM - edited Apr 03 2022 10:07 PM
What I thought that you want to highlight based on 3 criteria that you specify.
Now I understand that it is not what you want and what you want is to highlight all duplicate items if 3 conditions are met each other in the data table.
Apr 04 2022 03:01 AM
You're welcome @Robinmf1979! Glad it worked as desired.
If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.