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

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
Joined
Aug 18, 2015
Messages
10,739
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,739
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,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.
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