Conditional Format Duplicate in Two columns

[borolo222]

hi, I've been giving thought to this but I can't get to a solution. I need a conditional format that changes color of duplicates. I use this formula with one column,

=COUNTIF(A:A;A1)>1

but I need to give it one condition more, I need it to take into account the values in column B and change the color of cells in column A only to the ones that share the same values in A and in B. Example

Column A - Column B
1 - pp
2 - xx
3 - yy
2 - xx
2 - zz
2 - zz

In this case I only need highlighted the cells with 2 - xx (only if they are repeated)

Another thing to consider, The value in B is static, which means only the cells with XX in B that have the same values en A must be highlighted, that's why 2 -zz is repeated but not highlighted. haha Hope I was clear enough.

Thanks for your help.

 

[Akashwani]

Hi,

I guess you haven't had a reply because it is unclear as to what is in column A & B by the sample data you have posted.

Is this what you have?....

Excel Workbook
	A	B	C	D
1	Data A	Data B	*	CF Formula
2	1	PP	*	FALSE
3	2	XX	*	TRUE
4	3	YY	*	FALSE
5	2	XX	*	TRUE
6	2	ZZ	*	TRUE
7	2	ZZ	*	TRUE
Sheet3

If you can post some accurate sample data, a clear explanation of what you require and the expected results, I'm sure that you will get a solution.

Ak

 

[Maheshp]

Hi
Try in CF
=COUNTIF(B:B,A2)>0
you want to change the cell colour in column A if same value found in column B

 

[borolo222]

Thanks akashwani for your tip with the table. I'll use it from now on.

Maheshp, I tried your formula with no luck.

I want to highlight cells in column A that meet the following conditions:
1. Value is repeated
2. Value in column B is "XX" on the same row when condition 1 is met.

In the example below only cells A3 and A5 should be highlighted.

Thanks again,

Hi,

I guess you haven't had a reply because it is unclear as to what is in column A & B by the sample data you have posted.

Is this what you have?....

Excel Workbook
	A	B	C	D
1	Data A	Data B	*	CF Formula
2	1	PP	*	FALSE
3	2	XX	*	TRUE
4	3	YY	*	FALSE
5	2	XX	*	TRUE
6	2	ZZ	*	TRUE
7	2	ZZ	*	TRUE
Sheet3

If you can post some accurate sample data, a clear explanation of what you require and the expected results, I'm sure that you will get a solution.

Ak

 

[Akashwani]

Hi,

How do you get the repeated value in A?

Try this for your CF...

=AND(A2>1,B2="XX")

Ak

 

[borolo222]

Hi,

How do you get the repeated value in A?

Try this for your CF...

=AND(A2>1,B2="XX")

Ak

the value in A2 is an input from the user. the data in column A is the name of a project and the data in column B is the state of the project. Each project has different instances but can't have the same state. So when the user inserts the name of a project that is already on the list, the color of the cell gets highlighted if he puts a state that is already in for that project.

 

[borolo222]

Hey guys, I got it:

=AND(COUNTIFS($A:$A,$A1,$B:$B,"=XX")>1,$B1="XX")

First I count for each row if condition 1 and condition 2 are met, and then I check again if that row has the state that I want to compare to.