If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
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 |
#23
|
|||
|
|||
(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 ---- |
#24
|
|||
|
|||
Hi Max,
Email sent to your account - demechanik atyahoodotcom Thanks Sam -- Message posted via http://www.officekb.com |
#25
|
|||
|
|||
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 |
#26
|
|||
|
|||
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 |
#27
|
|||
|
|||
Sample file sent over, Sam !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- |
#28
|
|||
|
|||
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 ---- |
#29
|
|||
|
|||
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 ---- |
#30
|
|||
|
|||
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 ---- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count and Sum Total occurrances of two specific numbers | Sam via OfficeKB.com | Worksheet Functions | 10 | March 29th, 2005 08:13 PM |
count a group of numbers but do not count duplicates | Lisaml | Worksheet Functions | 2 | January 26th, 2005 11:19 PM |
How do I count a range of numbers in a column | SLB | General Discussion | 3 | October 21st, 2004 05:23 PM |
Count incidences of part numbers | jmdaniel | Worksheet Functions | 13 | March 9th, 2004 05:46 PM |