Hi Max,

Thank you for your time and assistance.

Biff's formula did the job requested and works based on my sample data.

Unfortunately, I did not take into account some single digit numbers in the
table. So when the formula looks in column C (cell C2) and sees 23, it
obviously thinks this is always twenty-three and not two and three. There
will however, be occassions when the digits in column C do actually mean
twenty-three and NOT two and three.

Can you think of a way to incorporate single digit numbers based on Biff's
formula. So that I can somehow distinguish between 2 AND 3 being 23 in
Column C, and 23 being truly twenty-three.

In Columns A and B for example:
A2=2 B2=3 Column C2=23

I get incorrect results when two single digits are paired together such as
2 AND 3 and the formula below counts the relevant Row Differences between
LAST occurrence and the PREVIOUS occurrence.

Now, calculate the the number of rows between the last
instance and the next to last instance.

In D2 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!L$20:L$27,LARGE(IF(ISNUMBER(SEARCH(C 2,Sheet1!
K$20:K$27)),ROW($1:$8)),1))-INDEX(Sheet1!L$20:L$27,LARGE(IF
(ISNUMBER(SEARCH(C2,Sheet1!K$20:K$27)),ROW($1:$8)) ,2))-1

Copy down as needed.

Note: In the formula, ROW($1:$8) refers to the range size
in rows. You'll need to tweak all the references to suit.



The number 23 would be paired with 24, so in Column C10 it would be 2324
A10=23 B10=24 C10=2324

Can the formula be salvaged from my mistake?

Thanks
Sam

--
Message posted via http://www.officekb.com