Compare help.

agentless

New Member
Joined
Jan 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Welcome to the MrExcel forum!

Try:

Book3
ABCD
1LocationDateTimeProcedure
2RAD *NA3/7/202014:15BN25ZZZ
3RAD *NA3/7/202014:15BW211ZZ
4RAD *NA3/7/202014:15BW29ZZZ
5RAD *NA3/7/202014:15B3281ZZ
6RAD *NA3/11/202023:25BW29ZZZ
7RAD *NA3/7/202014:15B3281ZZ
8RAD *NA3/11/202023:25BW29ZZZ
9RAD *NA3/11/202023:25 B3281ZZ
10RAD *NA3/16/202017:00B24BZZZ
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
Joined
Aug 18, 2015
Messages
10,750
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
Joined
Jan 15, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,473
Messages
5,624,933
Members
416,064
Latest member
PaulBr2

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
Top