find previous row for an item
 

Hi,

I'm looking for a method to find the previous row for an item, eg:
! A ! B
-----+-----+------------------
1 ! 0 ! Kurt
2 ! 0 ! Jan
3 ! 1 ! Kurt
4 ! 0 ! Peter
5 ! 2 ! Jan
6 ! 3 ! Kurt
7 ! 4 ! Peter
etc

Is this possible with a formule? Ifnot can i write a macro to perform this
operation?

thx

Hedwig

Put 0 in A1 (it'll always be 0), then copy the formula
below into A2, press ctrl + shift + enter, and fill down
the column:

=IF(COUNTIF($B$1:B1,B2),SMALL(IF($B$1:B1=B2,ROW
($B$1:B1)),COUNTIF($B$1:B1,B2)),0)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,

I'm looking for a method to find the previous row for an
item, eg:
! A ! B
-----+-----+------------------
1 ! 0 ! Kurt
2 ! 0 ! Jan
3 ! 1 ! Kurt
4 ! 0 ! Peter
5 ! 2 ! Jan
6 ! 3 ! Kurt
7 ! 4 ! Peter
etc

Is this possible with a formule? Ifnot can i write a
macro to perform this
operation?

thx

Hedwig
.

GREAT!!! THX!!!

"Jason Morin" wrote:

Put 0 in A1 (it'll always be 0), then copy the formula
below into A2, press ctrl + shift + enter, and fill down
the column:

=IF(COUNTIF($B$1:B1,B2),SMALL(IF($B$1:B1=B2,ROW
($B$1:B1)),COUNTIF($B$1:B1,B2)),0)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi,

I'm looking for a method to find the previous row for an
item, eg:
! A ! B
-----+-----+------------------
1 ! 0 ! Kurt
2 ! 0 ! Jan
3 ! 1 ! Kurt
4 ! 0 ! Peter
5 ! 2 ! Jan
6 ! 3 ! Kurt
7 ! 4 ! Peter
etc

Is this possible with a formule? Ifnot can i write a
macro to perform this
operation?

thx

Hedwig
.