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.

  • You mean to say apply Conditional Format on Sheet B match with Unique IDs in Sheet A ?? – Rajesh S Oct 1 '19 at 7:04

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.
  • Hi @Rajesh S, I think that the purpose of a solution is to compare all values in row to decide about some Group Number. Not only compare the Ref Number. – Muji Oct 1 '19 at 8:57
  • @Muji,, check the Screen shot above,, Sheet A Consolidation Ref No are duplicate compared with Sheet B Ref no are Unique,,, and entire rows have been highlighted,, ! – Rajesh S Oct 1 '19 at 9:00
  • @RajeshS Hi Rajesh, thank you very much for responding, I was less interested in highlighting the rows, I really want the unique ID to populate from the other sheet if the data in cells Ref Number + Amount 1-5 match the data in the corresponding sheet. But again thank you for responding, Sir, I really appreciate it. – Jack Constantine Oct 1 '19 at 11:14
  • Hi, @JackConstantine check the post now I've edited shows the method, to populate records contains unique ID,, but has duplicate data in cells Ref Number + Amount ,, I'm sure this work for you ☺ – Rajesh S Oct 2 '19 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.