Seeking advanced conditional formatting formula

[shepherdc2814]

Hi all,

See the attached spreadsheet, referenced is this request. In column A is a list of Site numbers, in which there are duplicates (site numbers can be repeated in several rows). In column B is the group number associated to a given site number.

I would like to create a conditional formatting formula that looks at all instances of a given site number, in column a, and if one instance of a site number is associated to either "200-103" or "200-104" in column B, then all rows with the same site number should be flagged. For example, since "Site 17" is associated to "200-104" I would want each row for "Site 17" to be flagged


Thanks in advance for any help with this!

[martindwilson]

i used this as the formula for conditional format
=OR(ISNUMBER(MATCH($A2&"200-103",INDEX($A$2:$A$200&$B$2:$B$200,0),0)),ISNUMBER(MATCH($A2&"200-104",INDEX($A$2:$A$200&$B$2:$B$200,0),0)))
is that what you mean? see attached

[FDibbins]

Try this under NEW RULE/Use Formula....
=IFERROR(MATCH(A2&"200-103",$A$2:$A$45&$B$2:$B$45,0),MATCH(A2&"200-104",$A$2:$A$45&$B$2:$B$45,0))

[shepherdc2814]

Hi, and thank you martindwilson! This is so close to what I need - I do not want to flag sites when they are only associated to 222-103 and/or 222-104. For example, sites 2,4,5,7, and 8 wouldn't be flagged. Does that make sense?

[shepherdc2814]

Thank you... Again, close, but I would like to exclude sites from being flagged when they are only associated to 222-103 and 222-104. is that possible?

[FDibbins]

For example, since "Site 17" is associated to "200-104" I would want each row for "Site 17" to be flagged

So you only want duplicates?

[FDibbins]

change the formula to this...

=IF(OR(COUNTIFS($A$2:$A$45,$A2,$B$2:$B$45,"200-103")=1,COUNTIFS($A$2:$A$45,$A2,$B$2:$B$45,"200-104")=1),"",IFERROR(MATCH(A2&"200-103",$A$2:$A$45&$B$2:$B$45,0),MATCH(A2&"200-104",$A$2:$A$45&$B$2:$B$45,0)))

[shepherdc2814]

A cluster of site numbers should be highlighted when: One of the rows has 222-103 or 222-104 as a group number, and there is at least one other row with different other group number(i.e. 110-102,110-104). Do not flag when a given site number is only associated to 222-103 or 222-104.

Sorry, seems to be a bit hard to explain. Hope that makes more sense.

[shepherdc2814]

FDibbins, that was correct!! you're a superstar! That's going to save a ton of manual labor. Thanks!

[FDibbins]

Happy to help and thanks for the feedback