# Compare help.

#### agentless

##### New Member
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
MSR 1 03/12/2020 11:47 0DJ08ZZ
MSR 2 03/14/2020 13:09 3E0G8GC

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Try:

Book3
ABCD
1LocationDateTimeProcedure
11MSR 13/12/202011:470DJ08ZZ
12MSR 23/14/202013:093E0G8GC
13
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:DExpression=COUNTIFS(\$A:\$A,\$A1,\$B:\$B,\$B1,\$C:\$C,\$C1,\$D:\$D,\$D1)>1textNO

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

#### Eric W

##### MrExcel MVP
Try:

Excel Formula:
``=(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

##### New Member
Try:

Excel Formula:
``=(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.

Replies
1
Views
46
Replies
0
Views
84
Replies
2
Views
37
Replies
5
Views
73
Replies
33
Views
680

1,126,888
Messages
5,621,417
Members
415,839
Latest member
Pollydooner

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

### Which adblocker are you using?

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

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