Excel – How to Match duplicate rows, with a unique ID – from a cloned and consolidated version of the same sheet

conditional formattingmicrosoft excelworksheet-function

I've been given ownership of a document to manage and it's a mess, I want to tidy it up, but have ventured out of my depth unfortunately 🙁

I have two sheets

  • Sheet A (many instances of rows with identical rows of data, each has
    an ID row (that ID has duplicates too))
  • Sheet B (a clone of Sheet A, with duplicate rows removed)

Going forward – Sheet A has to be removed, but while it does I have to reference what rows have been consolidated and into what rows on Sheet B

I want Sheet A to have a reference to a unique field on Sheet B, where the rows of data and the ref number are identical, this might be done with conditional formatting or formulae but I don't know how. (please see picture of desired effect)

What I would really like to happen

I need to be able to compare all the data in the row, but… Is it possible to pull in the reference from the consolidated sheet? from Sheet B to Sheet A?

"The three matches for Value B align to the reference "2" on the sheet, so therefore, the reference / value from that cell will be pulled / displayed / shown on the unconsolidated sheet"

Is this possible?

thanking anyone that even reads this in advance.

Best Answer

This solves the issue:

enter image description here

How it works:

  • Select required data range in Sheet B.
  • Reach to Conditional Formatting , New Rules & apply this Formula.

  • Apply an appropriate Cell Format and finish with Ok.


  • You need to flip the formula (sheet name & Cell references) if want to apply Conditional formatting on Sheet A.


Following method shows that how records contains unique IDs can be populated when few colums have has duplicated values in both Sheets.

enter image description here

  • An array Formula in Cell T78:


  • Finish formula with Ctrl+Shift+Enter & fill down.

  • Finally apply Auto Filter to get desire records.

enter image description here

  • Adjust Cell & Sheet references in the formula as needed.
Related Question