Highlight Duplicates

[LutonBarry]

Folks,

I have a spreadsheet that a colleague uses to order printer toner. We want to be able to detect duplicate orders, where the ordering location (the first three characters of the Order Number Col D), match the printer serial number (column F) and the ordered Part Number (Column I).

I have Concatenated this information in Column M, and have a formula in Column N to look back over the previous 121 lines and count if there is a match. The formula in Column L then displays "Duplicate" if the count for a particular line is >1.

This works fine in that it highlights the recent duplicate, but is it possibe for it to highlight as 'Duplicate' all instances of a duplicate within the previous 121 lines. I've highlighted some duplicates in Yellow and ideally I'd like to have all 4 lines marked as "Duplicate".

Many thanks.

[大灰狼1976]

Hi LutonBarry!
Change N1771 cell's formula to
"=COUNTIF(INDIRECT("M"&ROW()-121&":M"&LOOKUP(2,1/(M:M<>""),ROW(M:M))),M1771)"
and drag it up and down.

[LutonBarry]

Many thanks indeed. Genius absolute genius.

[大灰狼1976]

You're welcome