CODEWITHSUNDEEP
excelvba
Beans
Beans

Reputation: 139

Compare two Unordered Sheets then Highlight the Differences

I want to compare two unordered Excel sheets. The column order is exactly the same, but the order of rows can change and values in the row can change as well.

For example, this is Sheet 1 enter image description here

And this is sheet 2 enter image description here

Sheet 1 and Sheet 2 contain same projects (except sheet 2 have one newly added project (Q41312112)). Also, although the projects are same, the $ Amount changed for some projects on sheet 2, and the orders of projects also changed on sheet 2.

Most importantly, PO/SO CANNOT serve as the “unique identifier.” As you can see, there are two rows for PO/SO M16526136, the only difference is their “Activity”, which means “PO/SO” and “Activity” together serve as the unique identifier for each project.

Is there a way to compare two unorders sheets, and highlight the true differences? So, ideally, the result should look like this:

(Row 2 to 7 - although the order was different, these rows exist in sheet 1.
G3, G5, G7 are highlight because these projects $ Amount changed. Entire Row 8 is highlighted because row 8 itself is a new row.)

enter image description here

I am not even sure if this is possible in Excel, so any suggestions would be appreciated!

Upvotes: 1

Views: 1839

Answers (1)

BigBen
BigBen

Reputation: 50008

You can use two Conditional Formatting rules based on SUMIFS and COUNTIFS to do this:

Rule 1: To highlight amounts that differ:

  1. Formula: =SUMIFS(Sheet1!G:G,Sheet1!A:A,A2,Sheet1!E:E,E2)<>G2
  2. Applies to: G2:G10000. You could change the formula slightly and apply it to the entire column G if needed. enter image description here

Rule 2: To highlight new rows:

  1. Formula: =AND($A2<>"",COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$E:$E,$E2)=0)
  2. Applies to: A2:G10000. Again you could change this as needed. enter image description here

Upvotes: 1

Related Questions