Highlighting near duplicates?

[kinger15]

Hello, I am trying to find out how to identify near duplicates in a large excel sheet based on a monetary value. For example, If i have 2 rows I want to be able to highlight these as potential duplicates if the number is within certain criteria. Is there a formula that I can create to say:

If the same date, order#, invoice#, and within $5.00?

I want to be able to highlight this so I can easily look through these manually.

Thank you for your help.

DATE	ORDER#	INVOICE#	AMOUNT
2022-02-18	1234567	55667788	100.00
2022-02-18	1234567	55667788	95.00

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	DATE	ORDER#	INVOICE#	AMOUNT
2	18/02/2022	1234567	55667788	100
3	18/02/2022	1234567	55667788	95
4	17/02/2022	1234567	55667788	90
5
6
7
8
9
10
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D10	Expression	=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,"<="&$C2+5,$C$2:$C$10,">="&$C2-5)>1	text	NO

 

[kinger15]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1	DATE	ORDER#	INVOICE#	AMOUNT
2	18/02/2022	1234567	55667788	100
3	18/02/2022	1234567	55667788	95
4	17/02/2022	1234567	55667788	90
5
6
7
8
9
10
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D10	Expression	=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,"<="&$C2+5,$C$2:$C$10,">="&$C2-5)>1	text	NO

Thanks I will try that. I am just a beginner with excel. How would i apply this formula to a sheet with thousands of rows?

 

[Fluff]

Just select the rows you want the conditional to work on & change the ranges in the formula to suit.
So if you selected A2:D1000 you would use

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,"<="&$C2+5,$C$2:$C$10,">="&$C2-5)>1

 

[kinger15]

Just select the rows you want the conditional to work on & change the ranges in the formula to suit.
So if you selected A2:D1000 you would use

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,"<="&$C2+5,$C$2:$C$10,">="&$C2-5)>1

I really appreciate this! Thank you!

 

[Fluff]

You're welcome.