Excel formatting into a spill table, highlighting of unique items.

Question

In my "Master" sheet, I have a list of SKU's in Column "A", description on column "B", then Ingredient component numbers in "C", then that table repeats throughout my product codes. In my "lookup" sheet, I have it to where I type in a specific sku, and it brings that spill table over for me, just showing that SKU's information and picture.

(Code)

=LET(r,FILTER(Master!C:P,Master!$A:$A&""=$A$2&"",""),IF(r=0,"",r))

This works perfectly.

However, some SKUS have unique ingredients that only belong to that item. I want to be able to have that highlighted in the table in my "Lookup" sheet. I need to be able to quickly see if an ingredient is only used in that SKU and have it visibly show that for quick reference.

Example of "Master"

This information is pulled into my lookup sheet.

Example of my lookup sheet.

I want unique items to that SKU to be highlighted in this lookup table from my master sheet. I have tried some unique helpers, and formulas, but I can't seem to figure this one out.

Answer 1

Use conditional formatting with a COUNTIF-based rule that checks each ingredient against the full list of ingredients on the Master sheet, and apply that rule to the spilled range on the Lookup sheet.

Assume on the Lookup sheet the spilled ingredients are in column C (adjust as needed):

1. Select the ingredient cells in the spilled range on the Lookup sheet (for example C4:C100).
2. On the Home tab, select Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter a formula that tests whether the ingredient appears only once in the entire Master list. For example:

      =COUNTIF(Master!$C:$C, C4)=1
   

   • Master!$C:$C is the full ingredient column on the Master sheet.
   • C4 is the first ingredient cell in the Lookup spilled range.
5. Select Format, choose the fill/font format to highlight unique ingredients, and select OK.
6. Confirm with OK again.

Every ingredient that exists only once in Master!$C:$C (i.e., unique to that SKU) will now be highlighted in the Lookup sheet’s spilled table.

If the ingredients are in a different column or multiple columns, repeat the rule for each ingredient column, adjusting the cell reference (C4) and the Master ingredient column as needed.

---

References:

• Use conditional formatting to highlight information in Excel
• Filter for or remove duplicate values
• Filter for unique values or remove duplicate values