smokenack said:
column A is text
column B are dates
column C are names
column D are times
column E are place names as are column F
column G are numbers
column H are text
column I is text
all columns only contain dates, times , numbers or text. They are never mixed.
Define the following names, using Insert|Name|Define...
BigStr as referring to:
=REPT("z",255)
BigNum as referring to:
=9.99999999999999E+307
Then...
Book8 |
---|
|
---|
| A | B | C | D | E | F |
---|
1 | TEXT | DATE | | | Last Value | Pos Last Value |
---|
2 | IKF | 1/5/2005 | | N | GJH | 14 |
---|
3 | LLA | 12/10/2005 | | N-1 | BMF | 12 |
---|
4 | AOB | 7/18/2005 | | N-2 | FNF | 11 |
---|
5 | JGJ | 10/10/2005 | | | | |
---|
6 | | 12/18/2005 | | | | |
---|
7 | MOK | 7/1/2005 | | | | |
---|
8 | OAF | 9/21/2005 | | | | |
---|
9 | | 4/10/2005 | | | | |
---|
10 | HNB | 8/17/2005 | | | | |
---|
11 | CBB | 3/11/2005 | | | | |
---|
12 | FNF | 4/19/2005 | | | | |
---|
13 | BMF | 6/30/2005 | | | | |
---|
14 | | | | | | |
---|
15 | GJH | | | | | |
---|
|
---|
Formulas for the TEXT column...
E2, copied down:
=INDEX($A$2:$A$65536,F2)
F2:
=MATCH(BigStr,$A$2:$A$65536)
F3, copied down:
=MATCH(BigStr,$A$2:INDEX($A$2:$A$65536,F2-1))
For the DATE column, replace BigStr with BigNum.