Row by row comparison between 2 sheets - conditional formatting (changes/duplicates)

crtk09

New Member
Joined
Dec 17, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hey all! I'm trying to create a formula that can be used with conditional formatting to highlight changes, or duplicates, between 2 sheets, or 2 workbooks if that works better, on a row-by-row basis, for a week-by-week sales audit that we generate. I've tinkered with a bunch I've found online, but for whatever reason just cannot seem to find the "right one".

You'll find an example below with some dummy data (this is the exact formatting we use) to give you an idea of what I'm looking at. These sheets will often contain different orderings and new additions, as well as deletions of cancelled/completed orders. Any help would be greatly appreciated!

1 18 22:
MrExcel Sheet.xlsx
ABCDEFGHIJKLMNOP
1Customer NameOrder NumberQuote #Line NumberItem NumberOrdered QuantityBusiness UnitOrder Line TypeLine Flow StatusCreated ByL Creation DateActual Completion DateCurrencyUnit Selling PriceNetUSD Net
2ABC2000728IC-00127787-V1100019ROW-SALES-DEV0NetworkingSHIP_NO_CHARGECanceledN/A31-Dec-21USD2,4000.000.00
3DEF2000728IC-00127787-V1100020S-DEV-WFH1DevelopmentINVOICE_ONLYClosedN/A31-Dec-2128-Sep-21USD3,5003,500.003,500.00
4GHI2000986IC-0078103-0308900004SAL-ENG-QA2BroadcastIN_STOCKClosedN/A13-Oct-212-Dec-21USD00.000.00
5JKL2001611IC-0080368-0572600005QA-MOV-IN3NetworkingCREDIT_ONLYClosedN/A29-Dec-2130-Dec-21USD1,1851,185.301,185.30
6MNO2001052IC-0073713-0268000006ROW-SALES-DEV55DevelopmentIN_STOCKClosedN/A25-Oct-2116-Dec-21EUR1,1943,582.604,171.94
7MNO2001052IC-0073713-0267900005S-DEV-WFH2BroadcastIN_STOCKClosedN/A30-Oct-2116-Dec-21EUR1,1855,000.004,500.00
8PQR2000989No quote00007S-DEV-WFH66BroadcastIN_STOCKClosedN/A15-Oct-2122-Dec-21USD1,9603,920.003,920.00
9STU2262547IC-00125104-V800004SAL-ENG-QA1001NetworkingIN_STOCKClosedN/A11-Jul-2113-Sep-21USD1,09840,624.8940,624.89
10VW2000997IC-00126474-V200004QA-MOV-IN15.5DevelopmentIN_STOCKAwaiting ShipmentN/A15-Oct-2121-Dec-21USD2,100116,800.01116,800.01
11XYZ2000997IC-00126474-V200005QA-MOV-IN36.8BroadcastIN_STOCKAwaiting ShipmentN/A15-Oct-21USD2,10058,399.9958,399.99
11822


1 10 22:
MrExcel Sheet.xlsx
ABCDEFGHIJKLMNOP
1Customer NameOrder NumberQuote #Line NumberItem NumberOrdered QuantityBusiness UnitOrder Line TypeLine Flow StatusCreated ByL Creation DateActual Completion DateCurrencyUnit Selling PriceNetUSD Net
2ABC2000728IC-00127787-V1100019ROW-SALES-DEV0NetworkingSHIP_NO_CHARGECanceledN/A31-Dec-21USD2,4000.000.00
3DEF2000728IC-00127787-V1100020S-DEV-WFH1DevelopmentINVOICE_ONLYClosedN/A31-Dec-2128-Sep-21USD3,5003,500.003,500.00
4GHI2000986IC-0078103-0308900004SAL-ENG-QA1BroadcastIN_STOCKAwaiting ShipmentN/A14-Oct-212-Dec-21USD500500.00500.00
5JKL2001611IC-0080368-0572600005QA-MOV-IN1NetworkingCREDIT_ONLYClosedN/A29-Dec-21USD1,1851,185.301,185.30
6MNO2001052IC-0073713-0268000006ROW-SALES-DEV55DevelopmentIN_STOCKAwaiting ShipmentN/A25-Oct-21EUR1,1943,582.604,171.94
7PQR2000989No quote00004S-DEV-WFH21BroadcastIN_STOCKClosedN/A15-Oct-21USD1,9603,920.003,920.00
8STU2262547IC-00125104-V800004SAL-ENG-QA500NetworkingIN_STOCKClosedN/A11-Jul-2113-Sep-21USD1,09840,624.8940,624.89
9VW2000997IC-00126474-V200004QA-MOV-IN5DevelopmentIN_STOCKAwaiting ShipmentN/A15-Oct-2121-Dec-21USD2,100116,800.01116,800.01
10XYZ2000997IC-00126474-V200005QA-MOV-IN25BroadcastIN_STOCKAwaiting ShipmentN/A15-Oct-21USD2,10058,399.9958,399.99
11022
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Should probably clarify this a little more and to what I'm trying to accomplish:
-Find and compare a row by matching the various columns and their contents
-If anything has changed, highlight it
 
Upvote 0
Might be making headway on this, but would still love to hear from anyone regarding this! Tricky one considering the consistently changing data, orders they're delivered in, and the amount that I receive!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top