Match Last Occurrence of two numbers and Count to Previous Occurence

[Max]

Perhaps just email me at either of the 2 addresses below (both valid):
demechanik <at>yahoo<dot>com
xdemechanik <at>yahoo<dot>com

 

[Max]

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)

 

[Max]

(Remember to click F9 to recalc the data tables if ncess ...
sorry, "click F9" should read as: ... press F9 (key) ...

 

[Sam via OfficeKB.com]

Hi Max,

Email sent to your account - demechanik <at>yahoo<dot>com

Thanks
Sam

 

[Sam via 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(C2,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

 

[Sam via 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(C2,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

 

[Max]

Sample file sent over, Sam !

 

[Max]

Think I'd have to leave your follow-ons to Biff to respond, Sam <g>

 

[Max]

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.

 

[Max]

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 ..

 

[Sam via OfficeKB.com]

Hi Max & Biff,

Thank you for ongoing assistance.

Thanks for email attachment - will try amendments and post back in
Newsgroup.

Regards,
Sam

 

[Sam via OfficeKB.com]

Hi Max & Biff,

Biff's solution with the amendment using the TEXT Function worked well.

Max, I am also trying your solution and thank you for creating the
worksheet with the TEXT Function amendments to Biff's solution -
appreciated.

Thank you very much for all the help and your time.

Regards,
Sam