Conditional formatting to highlight first column if duplicates are found in another column

[Bimmy]

Hi,

Data is pasted in A Col.

Helper Columns -

Formulas in J and K Col extracts 2 keywords "ID" and "Date' from A Col
I Col reflects concatenated data of J and K Col.

Below is a snapshot of above explanation -



What currently works -

Currently if duplicate data are found in I Col then conditional formatting highlights them.

Below is a snapshot -



Assistance required for -

Is there anyway to highlight A Col if duplicate data are found in I Col

Have uploaded sample file for reference.

 

[pecoflyer]

Post a sample sheet please, pictures are utterly useless

 

[Bimmy]

Hi pecoflyer,

I already did a bit of searching but did not find any solution.

The thing is, 2 specific keywords "ID" and "Date" are extracted when data is pasted in A Col. Then these 2 keywords are combined in I Col and then a duplicate conditional formatting is applied.

From what I understood during these search is, duplicate conditional formatting works only if it is an exact match.

So in above case duplicates in I Col gets highlighted. How do I get conditional formatting to highlight A Col or
Columns A to I if duplicates are found in I Col.

 

[Bimmy]

Sample sheet already attached

 

[pecoflyer]

It took me a while to notice that all your cells were locked
If the format of your data is always the same, the attached should work, but I have a feeling there are going to be some additional features you will need?

 

[Bimmy]

Hi pecoflyer,

Your formula works PERFECTLY.

However, would like to understand the reason for the cells to be unlocked for the conditional formatting to work.
(By default I thought the cells are locked)

 

[pecoflyer]

I needed to unlock the cells to work on the sheet, that's all.

 

[Bimmy]

Hi pecoflyer,

Conditional Formatting formulas should return a "True" or "False".

Below snapshot is from the workbook that you have uploaded.
The formula returns a True if there are dupes and False if there are no dupes or if the cell is blank.
Also the color only highlights if there are dupes.
This is how it should work.



Below snapshot is from a new workbook. (I have unlocked all the cells in the sheet)
The formula returns a True if there are dupes and cells with no data. Formula returns a False only if there are no dupes
Also the color highlights if there are dupes and cells with no data



So, what am I doing wrong here. I have uploaded the workbook that is giving above result. Kindly go through the same and advise if there is anything else that I should do.

 

[p45cal]

try:
=IF($I2=" ",0,COUNTIF($I$2:$I$20,$I2))>1
for the conditional formatting of A2:A20
or if you have COUNTIFS:
=COUNTIFS($I$2:$I$20,$I2,$I$2:$I$20,"<> ")>1
I've put that formula in a monospaced font so that you can see the presence of the space which is important for this to work.