Duplicate values

[shaggy31]

Hi,

I need help regarding marking duplicate values in Excel. I know for Conditional formatting and highlighting duplicate values but I have one more condition so I don't know how to resolve it.

I have a table in which I keep data of devices that came into our service center. The data consists of income date, serial number, description, etc.
Now I would like to track and highlight the device (serial number) if it has come back to the center in the last six months (for example, we will see maybe it will be some different period but it doesn't matter for the question).

The table looks something like this:

Receive date	Serial No.	Client	Description	...
1.1.2023​	YYYYYYYYYY	Client1	sdfsdsd	...
14.1.2023​	XXXXXXXXXX	Client2	trettrgfd	...
28.2.2023​	ZZZZZZZZZZ	Client3	gfdgfdgfdg	...
1.3.2023​	AAAAAAAAAA	Client4	ghfgh5hfg	...
1.3.2023​	NNNNNNNNN	Client2	fgfdgbfdb	...
14.5.2023​	PPPPPPPPP	Client5	fgfdgdgdg	...
10.6.2023​	YYYYYYYYYY	Client1	fgfdgfdgfdgfd	...
7.7.2023​	XXXXXXXXXX	Client2	fgfdgdgfdg	...
...	...	...	...	...

In this example, the Serial number XXXXXXXXX should be highlighted because it was received again in the last six months, but YYYYYYYY shouldn't be because it has past six months.

Best regards,
Shaggy

 

[Anonymous1378]

How are you calculating 6 months? The difference between 7 July and 14 Jan is 174 days while the difference between 10 Jun and 1 Jan is 160 days, but the first one counts while the second one does not?

 

[shaggy31]

Sorry, for YYYYYYYYY it should be 10 July as a second date. My mistake.
It should be counting calendar days.

 

[Anonymous1378]

In that case, try this

Book12
	A	B	C	D	E
1	Receive date	Serial No.	Client	Description	...
2	01-Jan-23	YYYYYYYYYY	Client1	sdfsdsd	...
3	14-Jan-23	XXXXXXXXXX	Client2	trettrgfd	...
4	28-Feb-23	ZZZZZZZZZZ	Client3	gfdgfdgfdg	...
5	01-Mar-23	AAAAAAAAAA	Client4	ghfgh5hfg	...
6	01-Mar-23	NNNNNNNNN	Client2	fgfdgbfdb	...
7	14-May-23	PPPPPPPPP	Client5	fgfdgdgdg	...
8	10-Jul-23	YYYYYYYYYY	Client1	fgfdgfdgfdgfd	...
9	07-Jul-23	XXXXXXXXXX	Client2	fgfdgdgfdg	...
10	...	...	...	...	...
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=COUNTIFS($B$2:$B$9,B2,$A$2:$A$9,">="&EDATE(A2,-6),$A$2:$A$9,"<"&A2)	text	NO

 

[shaggy31]

In that case, try this

Book12
	A	B	C	D	E
1	Receive date	Serial No.	Client	Description	...
2	01-Jan-23	YYYYYYYYYY	Client1	sdfsdsd	...
3	14-Jan-23	XXXXXXXXXX	Client2	trettrgfd	...
4	28-Feb-23	ZZZZZZZZZZ	Client3	gfdgfdgfdg	...
5	01-Mar-23	AAAAAAAAAA	Client4	ghfgh5hfg	...
6	01-Mar-23	NNNNNNNNN	Client2	fgfdgbfdb	...
7	14-May-23	PPPPPPPPP	Client5	fgfdgdgdg	...
8	10-Jul-23	YYYYYYYYYY	Client1	fgfdgfdgfdgfd	...
9	07-Jul-23	XXXXXXXXXX	Client2	fgfdgdgfdg	...
10	...	...	...	...	...
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B9	Expression	=COUNTIFS($B$2:$B$9,B2,$A$2:$A$9,">="&EDATE(A2,-6),$A$2:$A$9,"<"&A2)	text	NO

Thanks!
I don't know if I'm doing it right. I copied your formula in Conditional Formatting Rules Manager like this, but for some reason, it doesn't work.

If I write a formula without quotes I receive an error message.

It is possible that I'm doing something wrong.

 

[Anonymous1378]

"">="" should just be ">=" and ""<"" should just be "<"
Wrapping the whole formula without quotes just makes it a text string, which does not help you in any way.

Additionally, I would expect the "Applies to" area to be the same height as your formula (i.e. 2:1000), unless there's something strange about your layout.

 

[shaggy31]

"">="" should just be ">=" and ""<"" should just be "<"
Wrapping the whole formula without quotes just makes it a text string, which does not help you in any way.

Additionally, I would expect the "Applies to" area to be the same height as your formula (i.e. 2:1000), unless there's something strange about your layout.

That's it, thank you...

Just one more question.
Excel has a limitation of 15 characters when comparing. Is there a way to increase this limit to 25 characters, because I have a situation where some devices have SN with more than 20 characters and in this case, Excel shows them as duplicate values because the first 15 characters are the same, but the rest isn't.

 

[Anonymous1378]

I'm assuming you're working with numbers here? Converting the cell range's format to Text BEFORE inputting the serial numbers will allow you to retain more than 15 digits.

 

[shaggy31]

Cells are formated as Text but never the less Excel compares only first 15 characters...

 

[Anonymous1378]

It's too late for the data you have already entered into excel; it would have already converted your digits after the 15th place into zeroes. You need to format the cell as text and re-input those numbers.