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.

    =NOT(ISERROR(MATCH(S57, SheetA!P:P,FALSE)))

  • Apply an appropriate Cell Format and finish with Ok.

N.B.

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

Edited:

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:

    {=IF(ISERROR(MATCH(O78&Q78&R78,SheetA!J:J&SheetA!L:L&SheetA!M:M,0)),"","Dupl")}

  • 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