Comparing 2 Excel Files for Duplicates

Sineadd

Board Regular
Joined
Apr 24, 2019
Messages
58
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi

Is there any simple formula i can use to compare 2 excel files so I can see if any lines are duplicated

Both excel files are like below and I need to check if each row to see if it exactly matches rows in another sheet and highlight these rows if all columns ae exact match

Order NumberOrder DateProduct NameDescriptionDispatched DateCostQtyCost total
12302-12-2021TableItems sold/delivered for order13 Jul 202101€0.00
12302-12-2021DeliveryItems sold/delivered for order13 Jul 2021100.151€100.15
45603-08-2021Nest of TablesItems sold/delivered for order13 Jul 202193.11€93.10
78903-08-2021BarstoolItems sold/delivered for order13 Jul 202151.952€103.90
78903-09-2021FootstoolItems sold/delivered for order13 Jul 202119.511€19.51
78903-09-2021Counter stoolItems sold/delivered for order13 Jul 202158.862€117.72

Thnaks
Sinead
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If you wanted to compare columns A-H, and you have Sheets 1 and 2, here is a Conditional Formatting formula you can use on Sheet1 to highlight any matching rows it finds on Sheet2.

First, select all your data, starting in cell A2 and going down to the last row in column H.
Then enter this Conditional Formatting formula:
Excel Formula:
=COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2,Sheet2!$C:$C,Sheet1!$C2,Sheet2!$D:$D,Sheet1!$D2,Sheet2!$E:$E,Sheet1!$E2,Sheet2!$F:$F,Sheet1!$F2,Sheet2!$G:$G,Sheet1!$G2,Sheet2!$H:$H,Sheet1!$H2)>0
and choose your desired formatting option.

Note that if it were me, I would probably use a "Matched Query" in Access to do this (as Access does these kind of things much better).
I do not use Power Query in Excel, but I am pretty sure that you could that to do this too.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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