M
Max
Perhaps just email me at either of the 2 addresses below (both valid):
demechanik <at>yahoo<dot>com
xdemechanik <at>yahoo<dot>com
demechanik <at>yahoo<dot>com
xdemechanik <at>yahoo<dot>com
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Match Last Occurrence of two numbers and Count to Previous Occurence
M
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)
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)
M
Max
(Remember to click F9 to recalc the data tables if ncess ...
sorry, "click F9" should read as: ... press F9 (key) ...
sorry, "click F9" should read as: ... press F9 (key) ...
S
Sam via OfficeKB.com
Hi Max,
Email sent to your account - demechanik <at>yahoo<dot>com
Thanks
Sam
Email sent to your account - demechanik <at>yahoo<dot>com
Thanks
Sam
S
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
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
S
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
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
M
Max
Sample file sent over, Sam !
M
Max
Think I'd have to leave your follow-ons to Biff to respond, Sam <g>
M
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.
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.
M
Max
Sam, maybe you'd like to try these 2 amendments to Biff's suggested set-up
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
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 ..
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 ..
S
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
Thank you for ongoing assistance.
Thanks for email attachment - will try amendments and post back in
Newsgroup.
Regards,
Sam
S
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
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
M
Max
You're welcome, Sam !
Glad to hear you got what you wanted
Thanks for the feedback ..
Glad to hear you got what you wanted
Thanks for the feedback ..
M
Max
.. There's a slight advantage in my suggested set-up said: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 ..
And the paired inputs in Sheet2's cols A and B need not be consecutive
numbers either, for example you could retrieve the results for say: 51, 79
or 73, 59 etc
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.