Match Last Occurrence of two numbers and Count to Previous Occurence

Perhaps just email me at either of the 2 addresses below (both valid):
demechanik atyahoodotcom
xdemechanik atyahoodotcom
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
"Sam via OfficeKB.com" wrote in message
...
Hi Max,

I did get a bit lost - would appreciate working sample.

How do I disguise my email address to avoid spammers?

Regards,
Sam

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

Ok, think parts of Biff's post might have been inadvertently distorted by
the interfaces. Here's a re-paste of the 2 parts you mentioned. But I'm not
sure whether this paste will again appear distorted from where you're
reading this, so I've included a "text" description of the formula below

In C2 enter this formula and copy down:
= A2&B2
(should read as: "Equal to" A2 & B2)

In K20 enter this formula and copy down:
= A20&B20&C20&D20&E20&F20&G20&H20&I20
(should read as: "Equal to" A20 & B20 & ... I20)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----
"Sam via OfficeKB.com" wrote in message
...
Hi Max,

Sorry to prolong the agony but in Biff's last posting there is a character
in front of the &B2 - it looks like the letter c with a line through it.
Is
it an abbreviation or something?

In C2 enter this formula and copy down:
?&B2?

In K20 enter this formula and copy down:
?0&B20&C20&D20&E20&F20&G20&H20&I20?

Regards
Sam

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

(Remember to click F9 to recalc the data tables if ncess ...
sorry, "click F9" should read as: ... press F9 (key) ...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

Hi Max,

Email sent to your account - demechanik atyahoodotcom

Thanks
Sam

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

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

Hi Biff,

Thank you for your time and assistance.

Your 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 your
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

Sample file sent over, Sam !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

Think I'd have to leave your follow-ons to Biff to respond, Sam g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

On 3rd thought g, some simplification .. in Sheet2, think that we could
dispense with setting up Data Table #3 altogether, and simply copy the
formula in E2 down col E, since the formulas in col E merely computes the
difference based on the values retrieved in cols C and D.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

Sam, maybe you'd like to try these 2 amendments to Biff's suggested set-up

On the sheet where the number table is:
In K20 enter this formula and copy down:
= A20&B20&C20&D20&E20&F20&G20&H20&I20

Put instead in K20:

=TEXT(A20,"00")&TEXT(B20,"00")&TEXT(C20,"00")&TEXT (D20,"00")&TEXT(E20,"00")&
TEXT(F20,"00")&TEXT(G20,"00")&TEXT(H20,"00")&TEXT( I20,"00")

Copy down to K480

On the Summary sheet:
In C2 enter this formula and copy down:
= A2&B2

Put instead in C2:

=TEXT(A2,"00")&TEXT(B2,"00")

Copy C2 down

(The rest of the constructs suggested by Biff remain unchanged)

The above should now enable Biff's solution to work for single digits as
well as double digits

Note that the paired inputs are still assumed in the summary sheet in cols A
and B, from row2 down. If you have 2 single digits as the paired inputs, say
2 and 3, enter these *separately* as per normal into A2:B2 in ascending
sequence (i.e. enter 2 in A2, 3 in B2) as you would for 2 double digits

... There's a slight advantage in my suggested set-up g: It enables you to
enter the paired inputs in Sheet2's cols A and B in any order, and also the
source data in Sheet1 need not be in ascending sequence [on a per row basis]
as well ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----