Excel - Highlighting Duplicate Values Sequentially?

Question

I am creating a scheduling worksheet that will highlight positions in the key while they are assigned to the employee in the schedule.

I know how to use the duplicate values conditional formatting where it will highlight cells of exact duplicate values, but I was wondering if anyone know how (without using Macros/VBA) to highlight repeated duplicate cells as it is added. I need this workbook to remain accessible via Excel Online.

See attached for an example of what I am trying to achieve. The left side has the duplicate values formatting applied. The right half is what I want to happen as similar positions are assigned to the lower box it will sequentially highlight cells in the box above.

Let mw know if you have any ideas!

Answer 1

As far as I can tell, this can be done with conditional formatting using a formula instead of the built-in “duplicate values” rule. The idea is to compare how many times a position appears in the schedule against how many times it appears up to each row in the key, so the highlighting “fills down” progressively.

Assume your key (the list you want to highlight) is in E2:E8, and your assigned schedule (bottom list) is in A12:A100.

Apply conditional formatting to E2:E8 using this formula:

=COUNTIF($A$12:$A$100,E2) >= COUNTIF($E$2:E2,E2)

This way the first COUNTIF counts how many times that position has been assigned in the schedule, and the second COUNTIF counts how many times that same position has appeared so far in the key list up to the current row. As assignments increase, more rows in the key meet the condition and get highlighted in order from top to bottom.

If your data ranges differ, adjust the references, but keep the structure the same with the expanding range on the key side ($E$2:E2) and fixed range on the schedule side.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

This can be done with conditional formatting that uses a formula instead of the built‑in “Duplicate Values” rule, which only highlights all duplicates the same way.

Example approach (no VBA, works in Excel for the web):

1. Assume the key/position list is in A2:A8 and the assigned positions are in D2:D8 (adjust ranges as needed).
2. Select the cells in the key that should highlight, e.g. A2:A8.
3. On the Home tab, select Conditional Formatting > New Rule.
4. Choose Use a formula to determine which cells to format (or in the “Classic” style, pick Format only unique or duplicate values and then switch to a formula rule as documented).
5. Enter a formula that checks whether the key value appears in the assignment range. For example:

      =COUNTIF($D$2:$D$8,LEFT($A2,SEARCH(",",$A2)-1)&"*")>=ROW()-ROW($A$2)+1
   

   Explanation (adapt to the exact text pattern in the screenshot):
   • LEFT($A2,SEARCH(",",$A2)-1)&"*" builds a wildcard pattern like "Server Close*" from "Server, Close 1A".
   • COUNTIF($D$2:$D$8, pattern) counts how many matching assignments exist in the lower box.
   • ROW()-ROW($A$2)+1 gives the sequential index of the current key row (1 for the first, 2 for the second, etc.).
   • The rule >= means: highlight the nth key row only when there are at least n matching assignments.
6. Click Format, choose the fill color (e.g., green), then OK to save the rule.
7. Copy/extend the rule if needed for other position groups (or adjust the pattern logic if the text structure differs).

As new matching entries are added in the assignment range, the COUNTIF result increases and the next corresponding key row meets the condition, so the highlighting moves sequentially without any VBA.

---

References:

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