Comparing 2 Excel Files for Duplicates

[Sineadd]

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 Number	Order Date	Product Name	Description	Dispatched Date	Cost	Qty	Cost total
123	02-12-2021	Table	Items sold/delivered for order	13 Jul 2021	0	1	€0.00
123	02-12-2021	Delivery	Items sold/delivered for order	13 Jul 2021	100.15	1	€100.15
456	03-08-2021	Nest of Tables	Items sold/delivered for order	13 Jul 2021	93.1	1	€93.10
789	03-08-2021	Barstool	Items sold/delivered for order	13 Jul 2021	51.95	2	€103.90
789	03-09-2021	Footstool	Items sold/delivered for order	13 Jul 2021	19.51	1	€19.51
789	03-09-2021	Counter stool	Items sold/delivered for order	13 Jul 2021	58.86	2	€117.72

Thnaks
Sinead

 

[Joe4]

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:

=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.