excelconcatenationconditional-formatting

Conditional Formatting Help - Compare Concat cells and Format Range/Table by row based on matches

I'm really hoping you can help me. I feel like the answer is just beyond my grasp and I've been smacking my brain against google for the last week trying to figure this out.

There is a report I pull for work multiple times daily. However, only a few lines on this report change during each pull. Since the report already has bright colors which designate what actions need to be taken, I want to conditionally format the duplicate rows so they they turn gray on the newly pasted data. This will allow me to focus only on the rows that need to be worked.

The catch is, once they're worked, new data is added and other data is removed when the background reporting script runs. So what happens is the the matching data won't be in the same rows as before.

To solve this I created a CONCAT column so that I only have to compare the concat values of each range row and try to format only the rows of the ranges that match.

I've tried formatting as range and as table, using different combinations of COUNTIFs, IFs, AND, and MATCH and I'm not getting the outcomes that I want.

In addition, because of the Concat value extending down beyond the range of the current data, those are also being marked as a "match" because of the formula. I need anything with a value of null to remain unformatted.

Any assistance you can provide would be MOST appreciated!!

RAW DATA: RAW DATA

IDEAL OUTCOME: enter image description here

---------------------------------------------------

9/30/24 Further Clarifications:

So the data is pulled at roughly 2 hour internals and the amount of data changes per data pull. Lines that existed previously, no longer exist, or there may be additional lines. Generally speaking there are roughly and average of 50-100 lines of data per pull.

Because I'm copying the file from another source, there is no Conditional Formatting currently set up in the file. This document is purely being used as a tracker for my progress for these data pulls and will be cleared of data each day.

I have attached a copy of the file and added a tab showing a very simplified version of the data pull variances. (I know the days of the week don't really match all the dates, they're irrelevant detail)

New data pulls are sorted by Date Ascending then by Name Ascending because of the secondary program used to work them.

CONCAT EXPERIMENT EXAMPLE FILE

In the example file on the Working Day Tab, the ideal outcome for the way the formatting should work has been applied manually.

---------------------------------------------------

@AxelRitcher Thanks for responding! I tried using the formula you suggested, I tried having the items turn purple to see the results compared to the ideal, but unfortunately nothing changed: Attempt 1

@Rotabor (UDPATED 10/1/24, again) Thanks again for your suggestion! I corrected my formula (silly mistake, sorry about that!) and now the data lines up correctly. AND I just found your updated version of the formula and it works GREAT!! This is EXACTLY what I needed. The formula ended up being about 2 layers more complicated than what I was originally trying to do, so I didn't go far enough. THANK YOU again!!

FINAL SOLUTION

Solution

  • enter image description here

    =NOT(ISERROR(XLOOKUP($E2&$F2,$C$2:$C$4,$C$2:$C$4)))

    This is formatting formula for E2:F5 when E2 is active: the row number in the first argument of XLOOKUP should match the row number of the active cell. You can add more cells in the XLOOKUP first argument.

    If you prefer to have the combination cell for the new data table too, just provide this cell as the XLOOKUP first argument.

    If any formatting rule affecting cell's fill already exists for the target range, you need to put the rule above first and check "Stop if True".

    But the most effective way for you can be to put the formula onto the worksheet and filter the range:

    enter image description here

    Update

    I took data from the workbook you provided:

    enter image description here

    It works fine. Also, I modified the formula to ignore blank rows:

    =AND(NOT(ISERROR(XLOOKUP($N4,$G4:$G20,$G4:$G20))),$N4<>"")

    What is wrong on your side:

    1. On your screenshot, I see NOT(ISERROR(XLOOKUP($N7,$G:$G,$G... but it should be $N4 not $N7. Your screenshot looks like mine but shifted up by 3 rows which is correspond to the difference between 7 and 4. You can select any cell on the row #4 and edit the rule.
    2. Other formatting rules - just to check. In Conditional Formatting Rules Manager, set "Show formatting rules for:" to "This Worksheet" and delete all unnecessary rules.

    Hope, it helps.