Highlight duplicate values when two other pairs of cells have matching values

[Rorando]

Greetings!

I'm trying to get find a way to highlight cells so I can see if duplicate values exist during the same day and time. This so I can easily spot these duplicates and change my dates/times in L column to avoid these.

In other words: How to highlight duplicate values in cell range: A1:K12, when L1 & L2 are both matching with any other combination of two cells in L Column, i.e. L4 & L5 / L7 & L8 / L10 & L11

An example here:

D1 & A10 Have duplicate values (33) in the picture. But I only want them highlighted because Their respective L Column values "Mon 13 Jan" & "13.00" match with L10 & L11

A7 & G10 Have duplicate values (39) in the picture, but since both their respective L column values don't match, I Don't want them highlighted.


I hope I am making myself clear enough to get some help in this matter.

All the best // R

 

[Peter_SSs]

I'm trying to get find a way to highlight cells so I can see if duplicate values exist during the same day and time.

If you are interested, you could do this with Conditional Formatting so there would be no need to re-run any code if any values in the range are added/changed and the shading would update automatically when/if values are changed.

I have added a helper column with O4 formula copied down as far as you might ever need. That column could then be hidden if you want.
Then select A4:K? (I have applied it to A4:K18 in case another section is added) and apply the Conditional Formatting shown.

It is easily expandable to any number of columns (& merged cells in columns L:N are not a problem)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3
4	6	13	26	33	43	47	127	202	216	222		13/01/2020			13/01/2020 1:00:00 PM
5	0	0	0	0	0	0	0	0	0	0	0	13:00			13/01/2020 1:00:00 PM
6	0	0	0	0	0	0	0	0	0	0	0				13/01/2020 1:00:00 PM
7	2	5	8	9	16	17	201	0	0	0	0	14/01/2020			14/01/2020 1:00:00 PM
8	0	0	0	0	0	0	0	0	0	0	0	13:00			14/01/2020 1:00:00 PM
9	0	0	0	0	0	0	0	0	0	0	0				14/01/2020 1:00:00 PM
10	39	40	46	48	49	50	54	56	69	72	74	14/01/2020			14/01/2020 1:00:00 PM
11	116	133	135	139	0	0	0	0	0	0	0	13:00			14/01/2020 1:00:00 PM
12	0	0	0	0	0	0	0	0	0	0	0				14/01/2020 1:00:00 PM
13	33	34	35	36	37	38	39	40	41	42	43	13/01/2020			13/01/2020 1:00:00 PM
14	295	309	310	337	0	0	0	0	0	0	0	13:00			13/01/2020 1:00:00 PM
15	0	0	0	0	0	0	0	0	0	0	0				13/01/2020 1:00:00 PM
16															13/01/2020 1:00:00 PM
17															13/01/2020 1:00:00 PM
18															13/01/2020 1:00:00 PM
Sheet1

Cell Formulas
Range	Formula
O4:O18	O4	=IF(COUNT(L4:N5)=2,L4+L5,O3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:K18	Expression	=AND(SUMPRODUCT(($A$4:$K$18=A4)*($O$4:$O$18=$O4))>1,A4<>0)	text	NO