Conditional to identify the duplicates based on the first value

[ramananhrm]

Hi,

I have a doubt on conditional formatting.

The Column a1 to a10 has company names listed & b1 to b10 has their products.

If the company name repeat more than once, it has to search the repeated product of the same company.

Example

In the range a1:a20 has three companies (c1, c2 & c3)
The c1 has to search the range b1:b20 for the duplicate product (c1p1, c1p1, c1p2, c2p2)

It has to format the duplicate value of the duplicate value of the c1.

Please help me out.

Regards,
Ramanan

 

[Peter_SSs]

It is not entirely clear to me.

1. What version of Excel are you using?

2. For this sample data do I have the correct cells highlighted? If not, which cells should be highlighted, and why?

Excel Workbook
	A	B
1	Company	Product
2	c1	p1
3	c2	p2
4	c3	p1
5	c3	p3
6	c2	p2
7	c2	p4
8	c2	p3
9	c3	p2
10	c1	p5
11	c1	p5
12	c2	p2
13	c3	p1
14	c2	p2
15	c1	p3
16
CF

 

[ramananhrm]

Hi Peter,

In the below mentioned example, the company Sony has a duplicate model 'Model 1' is highlighted from second time because the 'Model 1' is already exists in the fourth row.

The same way for the the company 'Samsung'. The 'Model 3' exist in the sixth row. It is repeating in the last row.

I hope this example give you a clear idea of how to conditionally format the duplicate value (Product) based on the first range.


Company Product
Sony Model 1
Nokia Model 1
Samsumg Model 1
Sony Model 1
Nokia Model 2
Samsumg Model 3
Sony Model 3
Nokia Model 1
Samsumg Model 4
Sony Model 5
Nokia Model 6
Samsumg Model 7
Sony Model 1
Nokia Model 9
Samsumg Model 10
Sony Model 11
Nokia Model 12
Samsumg Model 3

 

[Peter_SSs]

That clarifies Q2 but you didn't answer Q1.

 

[ramananhrm]

Im using 2007 version.

So sorry for not answering ur first question.

Thanks,
Ramanan

 

[Peter_SSs]

Select B2:B?? and apply the CF shown.

Excel Workbook
	A	B
1	Company	Product
2	Sony	Model 1
3	Nokia	Model 1
4	Samsumg	Model 1
5	Sony	Model 1
6	Nokia	Model 2
7	Samsumg	Model 3
8	Sony	Model 3
9	Nokia	Model 1
10	Samsumg	Model 4
11	Sony	Model 5
12	Nokia	Model 6
13	Samsumg	Model 7
14	Sony	Model 1
15	Nokia	Model 9
16	Samsumg	Model 10
17	Sony	Model 11
18	Nokia	Model 12
19	Samsumg	Model 3
CF 2007+

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2)	Abc

 

[ramananhrm]

Hi Peter,

Thank you very much.

It really worked.

I have made a minor change in the formula.

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1

Thank you very much for the help.

Thanks.
Ramanan

 

[Peter_SSs]

That should work but I'm not sure why you made that change.

 

[jim may]

=COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2)>0
worked for me

 

[Peter_SSs]

=COUNTIFS($A$1:$A1,$A2,$B$1:$B1,$B2)>0
worked for me

That also works, but you don't need the >0 comparison - per my original formula.