Conditional Formatting

[sasquach]

Please help

I have to compare two columns and color fill a cell depending on a condition.

The comparison is between 2 columns. One column is states and the other column is companies. I need to fill in all company cells which match 3 or more states. In other words if a company is duplicated for 3 or more different state entries in the state column, the company column needs to be colored yellow for example.

Thanks in advance

 

[sasquach]

Grettings,

Just wanted to add and example

State Company
<TABLE style="WIDTH: 357pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=475><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 280pt; mso-width-source: userset; mso-width-alt: 13641" width=373><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=102>IA</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 280pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=373>Ace</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 357pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=475><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 280pt; mso-width-source: userset; mso-width-alt: 13641" width=373><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=102>MI</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 280pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=373>Ace</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 357pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=475><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 280pt; mso-width-source: userset; mso-width-alt: 13641" width=373><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=102>MN</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 280pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=373>Ace</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 357pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=475><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 280pt; mso-width-source: userset; mso-width-alt: 13641" width=373><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=102>NE</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 280pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=373>Action</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 357pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=475><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 280pt; mso-width-source: userset; mso-width-alt: 13641" width=373><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 height=20 width=102>MI</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 280pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl69 width=373>Valley</TD></TR></TBODY></TABLE>
In this example I would like to color the cell for Ace yellow since it is duplicated for 3 states.

Sasquach

 

[foolios]

The way I did it was if your data you want to find duplicates of was in column B then put this code in column C:

=COUNTIF(B$1:B986, B1)

Column B row 1 would check all rows in the range below up until B986 for any values matching B1. I drag/copy B1 cell's formula down the worksheet and it will update for each row.

Then I set up a conditional format that if any row in column B has a value of greater than 1, it should turn red.
That tells me that there are duplicates.

 

[foolios]

Wups, that should read, if any value in column C is greater than 1, the conditional format should change the cells color to red.
Also, the formula is in Column C, so drag down column C to fill the rows for similar checks.

Hope that helps.

 

[p45cal]

I couldn't work out a way to do this with just built in worksheet functions, so I made a user defined function (UDF) called ContStates which returns the number of unique states associated with a given company.
Below, you don't need column I at all, it's just there to help me develop and to illustrate the formula working which is later used in conditional formatting.
Use it as follows:
=CountStates(G2,$G$2:$G$20,$F$2:$F$20)>2
where the first argument is the single company name, the second the full list of company names, and the third argument the corresponding statenames:
<embed src="https://www.box.net/embed/21hkj0ddxotas5c.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

You don't need to do this for your purposes, but to put it into conditional formatting, select the whole range of company names, selecting by starting at the top so that the active cell is in the topmost row, then using the Use a Formula to… option (Formula Is: in pre Exel 2007) and just paste in the whole formula, including the preceding equals sign:
<embed src="https://www.box.net/embed/vbl6nmd71cftsy3.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

and you get:

Excel Workbook
	F	G	H	I
1	State	Co name
2	State1	Co1		TRUE
3	State1	Co1		TRUE
4	State3	Co3		TRUE
5	State4	Co4		FALSE
6	State5	Co5		TRUE
7	State1	Co1		TRUE
8	State7	Co1		TRUE
9	State8	Co1		TRUE
10	State4	Co4		FALSE
11	State1	Co5		TRUE
12	State1	Co1		TRUE
13	State3	Co2		FALSE
14	State4	Co3		TRUE
15	State5	Co3		TRUE
16	State6	Co5		TRUE
17	State1	Co1		TRUE
18	State8	Co2		FALSE
19	State9	Co3		TRUE
20	State4	Co4		FALSE
Sheet12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2	1. / Formula is =CountStates(G2,$G$2:$G$20,$F$2:$F$20)>2	Abc

Now all this will fail unless it's backed up by the UDF itself in a standard code module:

Function CountStates(CoName, CoNames, StateNames)
Set xxx = CreateObject("Scripting.dictionary")
i = 0
For Each cll In CoNames.Cells
    i = i + 1
    If cll.Value = CoName.Value Then
        If Not xxx.Exists(StateNames(i).Value) Then xxx.Add StateNames(i).Value, StateNames(i).Value
    End If
Next cll
CountStates = xxx.Count
End Function

 

[sasquach]

p45cal,
Thanks, for the input. The script worked perfectly. I really appreciate your assistance.