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

 

[shaggy31]

No, you didn't understand me... Data was inputted as Text from the start, but still Excel looks at only the first 15 characters.
The format of the data is o.k. (example: 123456789012345678901234 and 123456789012345678943210), but Excel compares the first 15 characters (bold) and says it is duplicate, but it isn't.

 

[Anonymous1378]

Ahhh, I see what you mean; the COUNTIF function must be converting your text value back into a number; if your values are unique enough, you could ensure they remain as text by putting a wildcard in front of the cell reference, such as $B$2:$B$9,"*"&B2 instead of $B$2:$B$9,B2. Otherwise, avoid COUNTIF altogether with something like

=MATCH(1,($B$2:$B$9=B2)*($A$2:$A$9>=EDATE(A2,-6))*($A$2:$A$9<A2),0)

 

[shaggy31]

Thanks!