| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 . |
|
#3
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using the Find tool in EXCEL | Excel Worksheet Functions | |||
| How do I find data from a list (or table) and insert it in a row? | Excel Discussion (Misc queries) | |||
| find replace cursor default to find box | Excel Discussion (Misc queries) | |||
| Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
| VB Find and Replace | Excel Worksheet Functions | |||
Linear Mode
