Compare help.

[agentless]

Hi I need some help doing something that is way over my head.

I need to compare data with the same location, time date and procedure which are in a row in different cells against the next row and if there is a match of all 4 cells then format.

Example below the 4th and 6th row are identical but I cannot get duplicates to compare across.

Line 8 has the same procedure but not the same date and time, i don't want that to highlight.

Any help would be appreciated. Al

Location Date Time Procedure
RAD *NA 03/07/2020 14:15 BN25ZZZ
RAD *NA 03/07/2020 14:15 BW211ZZ
RAD *NA 03/07/2020 14:15 BW29ZZZ
RAD *NA 03/07/2020 14:15 B3281ZZ
RAD *NA 03/11/2020 23:25 BW29ZZZ
RAD *NA 03/07/2020 14:15 B3281ZZ
RAD *NA 03/11/2020 23:25 BW29ZZZ
RAD *NA 03/11/2020 23:25 B3281ZZ
RAD *NA 03/16/2020 17:00 B24BZZZ
MSR 1 03/12/2020 11:47 0DJ08ZZ
MSR 2 03/14/2020 13:09 3E0G8GC

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D
1	Location	Date	Time	Procedure
2	RAD *NA	3/7/2020	14:15	BN25ZZZ
3	RAD *NA	3/7/2020	14:15	BW211ZZ
4	RAD *NA	3/7/2020	14:15	BW29ZZZ
5	RAD *NA	3/7/2020	14:15	B3281ZZ
6	RAD *NA	3/11/2020	23:25	BW29ZZZ
7	RAD *NA	3/7/2020	14:15	B3281ZZ
8	RAD *NA	3/11/2020	23:25	BW29ZZZ
9	RAD *NA	3/11/2020	23:25	B3281ZZ
10	RAD *NA	3/16/2020	17:00	B24BZZZ
11	MSR 1	3/12/2020	11:47	0DJ08ZZ
12	MSR 2	3/14/2020	13:09	3E0G8GC
13
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:D	Expression	=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1,$D:$D,$D1)>1	text	NO

Edit: Nuts, I just realized that COUNTIFS arrived in Excel 2019, and you say you have 2016. Let me think some more.

 

[Eric W]

Try:

=(AGGREGATE(15,6,ROW($A$1:$A$100)/($A$1:$A$100=$A1)/($B$1:$B$100=$B1)/($C$1:$C$100=$C1)/($D$1:$D$100=$D1),1)<>AGGREGATE(14,6,ROW($A$1:$A$100)/($A$1:$A$100=$A1)/($B$1:$B$100=$B1)/($C$1:$C$100=$C1)/($D$1:$D$100=$D1),1))*($A1<>"")

On this one you'll need to change the 100 in the formula to something that's beyond your last row of data.

 

[agentless]

Try:

=(AGGREGATE(15,6,ROW($A$1:$A$100)/($A$1:$A$100=$A1)/($B$1:$B$100=$B1)/($C$1:$C$100=$C1)/($D$1:$D$100=$D1),1)<>AGGREGATE(14,6,ROW($A$1:$A$100)/($A$1:$A$100=$A1)/($B$1:$B$100=$B1)/($C$1:$C$100=$C1)/($D$1:$D$100=$D1),1))*($A1<>"")

On this one you'll need to change the 100 in the formula to something that's beyond your last row of data.

Thanks, I will give it a shot and advise.