Consecutive problems!

[paulontour]

Hi

I have a table which has entries made every day. The entries would be a name in Col A, whether something was "Suitable" in Col B and a date in Column C. I would like a formula or a pivot table to count back and show the number of consecutive "Suitable" entries. However, if an entry in Col B is "Unsuitable", I would like it to start again (therefore it has to be a rolling count).

The table created would need to show the name of the person, followed by the number of consecutive "Suitable" entries to date.

Hope you can help

Paul.

 

[markmzz]

A small modification on Domenic's formula:

=IF(ROWS(E$2:E2)<=SUMPRODUCT(--($A$2:$A$15=E$1),--ISNUMBER($C$2:$C$15)),INDEX($B$2:$B$15,LARGE(IF($A$2:$A$15=E$1,IF(ISNUMBER($C$2:$C$15),ROW($B$2:$B$15)-ROW($B$2)+1)),ROWS(E$2:E2))),"")

Markmzz

 

[Domenic]

A small modification on Domenic's formula:

=IF(ROWS(E$2:E2)<=SUMPRODUCT(--($A$2:$A$15=E$1),--ISNUMBER($C$2:$C$15)),INDEX($B$2:$B$15,LARGE(IF($A$2:$A$15=E$1,IF(ISNUMBER($C$2:$C$15),ROW($B$2:$B$15)-ROW($B$2)+1)),ROWS(E$2:E2))),"")

Markmzz

Thanks Mark, I overlooked it...

 

[markmzz]

Thanks Mark, I overlooked it...

You are welcome.

Markmzz