Flag each group in col C if one or more flag in col A

**oldchippy** · 10-16-2008, 11:38 AM

Hi,

Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.

Any offers?

Cheers

**Domenic** · 10-16-2008, 06:21 PM

Try...

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(B2<>"",IF(SUM(IF($B$2:$B$19=B2,IF(ISTEXT($A$2:$A$19),IF($A$2:$A$19="Y",1)))),"Y",""),"")

Hope this helps!

**oldchippy** · 10-16-2008, 06:28 PM

Well Domenic, thanks very much, thats brilliant - 10/10

**Domenic** · 10-16-2008, 06:45 PM

You're very welcome! Glad I could help!

**oldchippy** · 10-16-2008, 06:47 PM

May be you could help a little more, I've been trying to get my head around how this works - could you explain?

**Domenic** · 10-16-2008, 07:13 PM

For the formula in C2, the SUM part of the formula counts the number of times a value in Column B equals B2, and where the corresponding value in Column A is a text value and equals "Y".

If SUM returns any number, other than 0, the second IF statement is evaluated as TRUE and "Y" is returned. If SUM returns 0, the second IF statement is evaluated as FALSE and a null string is returned.

The first IF statement is included so that the second IF statement does not have to be evaluated for any row where the corresponding cell in Column B is empty. For these cases, a null string is returned.

When the formula is copied to the next cell below, the same process takes place. The difference being that =B2 changes to =B3. And so on as the formula is copied to each cell below.

Hope this helps!

**Trebor76** · 10-16-2008, 07:54 PM

Hi oldchippy,

Though I can see Domenic has provided you the solution (and a very clever one at that) here's my attempt:

Please Login or Register  to view this content.

I had to resort to code to cater for the dynamic ranges but then simply used the COUNTIF function. Just ensure the cursor is at the first output cell (C2, D2...) and then run the macro.

HTH

Robert