Highlight duplicates based on date range in another column

[neilskinner]

Hello,

Can anyone help me with a conditional formatting formula to highlight duplicate data in column H, but only if data in Column I (dates) are at least 2 weeks apart? We are keeping track on railcar numbers and their dates of arrival. If there is a duplicate entry of a railcar but the arrival dates are greater than two weeks apart, we know that this is accurate. However, if arrival dates are within two weeks we know that this data is duplicated and should be highlighted. Any help would be greatly appreciated!

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B
1	Car	Date
2	123	1-Jan
3	456	2-Jan
4	789	3-Jan
5	123	1-Feb
6	456	2-Feb
7	123	10-Feb
8	222	1-Mar
9	456	1-Mar
10	333	4-Mar
11	456	8-Mar
12
13
14
15
16
17
18
19
20
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A19	Expression	=(A2<>"")*((B2-MAX(IF((A$2:A$20=A2)*(B$2:B$20<B2),B$2:B$20))<14)+(MIN(IF((A$2:A$20=A2)*(B$2:B$20>B2),B$2:B$20,99999))-B2<14))	text	NO

 

[neilskinner]

Thank you so much!

 

[neilskinner]

=(h2<>"")*((k2-MAX(IF((h$2:h$20000=h2)*(k$2:k$20000<k2),k$2:k$20000))<14)+(MIN(IF((h$2:h$20000=h2)*(k$2:k$20000>k2),k$2:k$20000,99999))-k2<14))

Eric, thanks for your help yesterday. I basically just changed your cells to my cells. Your "A" cell is my "H" cell. Your "B" cell is my "K" cell. Do you see anything off in the above formula? Do I need to have the date column in a certain format for the formula to run correctly? I am getting some highlights, but they aren't highlight duplicates. I'm guessing that I transposed something wrong when I typed in the formula into the box.

 

[Eric W]

Your adjustments to the formula are correct.

Book1
	H	I	J	K
1	Car			Date
2	123			1-Jan
3	456			2-Jan
4	789			3-Jan
5	123			1-Feb
6	456			2-Feb
7	123			10-Feb
8	222			1-Mar
9	456			1-Mar
10	333			4-Mar
11	456			8-Mar
12
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H2000	Expression	=(H2<>"")*((K2-MAX(IF((H$2:H$2000=H2)*(K$2:K$2000<K2),K$2:K$20))<14)+(MIN(IF((H$2:H$2000=H2)*(K$2:K$2000>K2),K$2:K$2000,99999))-K2<14))	text	NO

My biggest suspicion is that there's an issue with the date column. How are the dates stored? Are they an actual Excel date (numeric, but formatted as a date), or a text representation of a date?

 

[neilskinner]

I think originally they are formatted as "date". I changed the whole column to "text" and it changed the dates to 5 digit numbers, but to my knowledge, it didn't seem to make a difference

 

[Eric W]

Are you able to provide a sample of your sheet, with your data? If you could use the XL2BB tool (see the link in my signature or the reply box), it would greatly facilitate the process. It's easy to download, install and use. My samples above used it.

 

[neilskinner]

Fastweigh Rail Cars.xlsx
	H	I	J	K
1	RAIL CAR	WEIGHTS	TONS	ARRIVED
2	ACFX 220125	147859	73.9295	1/11/2022
3	ACFX 220136	150887	75.4435
4	ADMX 1013	185950	92.975	4/4/2022
5	ADMX 112	187680	93.84
6	ADMX 21005	191760	95.88	3/30/2022
7	ADMX 21010	191740	95.87	3/27/2022
8	ADMX 25402	186600	93.3	4/21/2022
9	ADMX 25432	186360	93.18	4/25/2022
10	ADMX 25475	186320	93.16	4/13/2022
11	ADMX 25615	186720	93.36	3/25/2022
12	ADMX 25828	187140	93.57	4/4/2022
13	ADMX 26198	186720	93.36	4/17/2022
14	ADMX 26413	186480	93.24	3/30/2022
15	ADMX 30267	196280	98.14
16	ADMX 30273	196740	98.37	4/21/2022
17	ADMX 30731	221160	110.58
18	ADMX 30733	220200	110.1	4/13/2022
19	ADMX 30734	221240	110.62	4/6/2022
20	ADMX 30796	221180	110.59	4/21/2022
21	ADMX 30920	221160	110.58	4/1/2022
22	ADMX 30938	220240	110.12	4/8/2022
23	ADMX 30949	221120	110.56	4/6/2022
24	ADMX 30998	221120	110.56	4/8/2022
25	ADMX 31045	221180	110.59	4/17/2022
26	ADMX 31062	220780	110.39	4/2/2022
27	ADMX 31074	220260	110.13	4/1/2022
28	ADMX 31076	220820	110.41	3/25/2022
29	ADMX 31114	220640	110.32
30	ADMX 31180	221480	110.74	3/20/2022
31	ADMX 31190	198100	99.05	4/17/2022
32	ADMX 31224	221400	110.7	4/2/2022
33	ADMX 31228	220920	110.46	3/19/2022
34	ADMX 31256	220660	110.33
35	ADMX 31280	221340	110.67	3/27/2022
36	ADMX 31280	220820	110.41
Sheet1

Cell Formulas
Range	Formula
J2:J36	J2	=I2/2000

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:H	Expression	=(H2<>"")*((K2-MAX(IF((H$2:H$20000=H2)*(K$2:K$20000<K2),K$2:K$20000))<14)+(MIN(IF((H$2:H$20000=H2)*(K$2:K$20000>K2),K$2:K$20000,99999))-K2<14))	text	NO

 

[Eric W]

Thank you for doing that, I was able to spot right off what was off.

Book1
	H	I	J	K
1	RAIL CAR	WEIGHTS	TONS	ARRIVED
2	ACFX 220125	147859	73.9295	1/11/2022
3	ACFX 220136	150887	75.4435
4	ADMX 1013	185950	92.975	4/4/2022
5	ADMX 112	187680	93.84
6	ADMX 21005	191760	95.88	3/30/2022
7	ADMX 21010	191740	95.87	3/27/2022
8	ADMX 21010	186600	93.3	4/2/2022
9	ADMX 25432	186360	93.18	4/25/2022
10	ADMX 25475	186320	93.16	4/13/2022
11	ADMX 25615	186720	93.36	3/25/2022
12	ADMX 25828	187140	93.57	4/4/2022
13	ADMX 26198	186720	93.36	4/17/2022
14	ADMX 26413	186480	93.24	3/30/2022
15	ADMX 30267	196280	98.14
16	ADMX 30273	196740	98.37	4/21/2022
17	ADMX 30731	221160	110.58
18	ADMX 30733	220200	110.1	4/13/2022
19	ADMX 30734	221240	110.62	4/6/2022
20	ADMX 30796	221180	110.59	4/21/2022
21	ADMX 30920	221160	110.58	4/1/2022
22	ADMX 30938	220240	110.12	4/8/2022
23	ADMX 30949	221120	110.56	4/6/2022
24	ADMX 30998	221120	110.56	4/8/2022
25	ADMX 31045	221180	110.59	4/17/2022
26	ADMX 1013	220780	110.39	4/2/2022
27	ADMX 31074	220260	110.13	4/1/2022
28	ADMX 31076	220820	110.41	3/25/2022
29	ADMX 31114	220640	110.32
30	ADMX 31180	221480	110.74	3/20/2022
31	ADMX 31190	198100	99.05	4/17/2022
32	ADMX 31224	221400	110.7	4/2/2022
33	ADMX 31228	220920	110.46	3/19/2022
34	ADMX 31256	220660	110.33
35	ADMX 31280	221340	110.67	3/27/2022
36	ADMX 31280	220820	110.41
Sheet12

Cell Formulas
Range	Formula
J2:J36	J2	=I2/2000

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:H	Expression	=(H1<>"")*(K1<>"")*((K1-MAX(IF((H$1:H$20000=H1)*(K$1:K$20000<K1),K$1:K$20000))<14)+(MIN(IF((H$1:H$20000=H1)*(K$1:K$20000>K1),K$1:K$20000,99999))-K1<14))	text	NO

The main difference was that when I selected the range to apply the Conditional Formatting to, I selected H2:H20. You selected the entire H column. When you create a Conditional Formatting formula, you need to make the cell reference in it refer to the upper left corner of the selected range. In my case, that was H2, in yours, H1. That's the biggest reason it didn't work right. I also added another condition at the start that made sure that the K column has a date in it, since you have several rows that don't.

There are 2 reasons I chose H2:H20 as my range. The first is so that H1 wouldn't be highlighted, but it didn't really matter in this case. The second is performance. You'll probably notice that your sheet is a bit sluggish at times. When Excel has to calculate a million rows of data with an array formula, that can take a while. You might get a bit better performance if you keep the formula as is, but just apply it to H1:H20000.

Let me know how it works!

 

[neilskinner]

i really appreciate the help. I posted your changes, but I still cant seem to get anything. Sorry to be a bother! Here is another snippet. I feel like it should be highlighting these duplicates but it isnt.

Fastweigh Rail Cars.xlsx
	H	I	J	K
326	CEFX 360182	201150	100.575	4/25/2022
327	CEFX 360182	201150	100.575	4/25/2022
328	CEFX 360203	200200	100.1	4/25/2022
329	CEFX 360203	200200	100.1	4/25/2022
330	CEFX 360216	200500	100.25	4/25/2022
331	CEFX 360216	200500	100.25	4/25/2022
332	CEFX 360256	201100	100.55	4/25/2022
333	CEFX 360261	204100	102.05
334	CEFX 360283	213550	106.775
335	CEFX 360310	209000	104.5	4/21/2022
336	CEFX 360321	216500	108.25
337	CEFX 360328	212450	106.225
338	CEFX 360330	208300	104.15
Sheet1

Cell Formulas
Range	Formula
J326:J338	J326	=I326/2000

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:H	Expression	=(H1<>"")*(K1<>"")*((K1-MAX(IF((H$1:H$20000=H1)*(K$1:K$20000<K1),K$1:K$20000))<14)+(MIN(IF((H$1:H$20000=H1)*(K$1:K$20000>K1),K$1:K$20000,99999))-K1<14))	text	NO