Match Last Occurrence of two numbers and Count to Previous Occurence
Hi All,
Thank you very much for taking the time to provide various formulas. I need to find /match the LAST time two specific numbers appeared together and Count the number of Rows between the LAST time they appeared together back to their PREVIOUS appearance together. I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20- 480). I have made the table a Dynamic Range called "Numbers". Each Row contains numbers in ascending order. Is there a formula that can check for two specific numbers Row by Row through the (nine column) Range "Numbers" and Return the Row Count of their LAST appearance together back to their PREVIOUS appearance together, from the Dynamic Range "Numbers"? Columns A-I = Dynamic Range "Numbers" Rows 20-480 Example sample data from Range "Numbers": ROW20 51 58 59 65 69 72 73 76 79 ROW31 50 51 58 72 73 76 79 80 81 ROW50 50 52 60 62 68 69 70 75 76 ROW75 53 54 59 60 62 69 70 72 73 ROW80 50 51 58 59 70 71 72 73 76 ROW83 51 53 65 67 68 69 78 80 81 ROW94 51 52 58 60 61 65 67 72 73 ROW99 50 53 57 62 63 68 70 71 73 Example Criteria: Locate when 72 AND 73 LAST appeared together and Count back to their PREVIOUS appearance together to get the required Count; i.e. the number of Rows in between the LAST appearance and the PREVIOUS appearance. Count from the Row above LAST appearance to the Row before PREVIOUS appearance. Expected Result: 72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95) Count=4 50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32) Count=48 68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51) Count=32 80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32) Count=51 Regards, Sam -- Message posted via http://www.officekb.com |
Hi!
ROW99 50 53 57 62 63 68 70 71 73 72 AND 73 LAST Appear=ROW99 Is that a typo? Also, I notice that the numbers you're looking for are consecutive: 72 AND 73 50 AND 51 68 AND 69 80 AND 81 Is that always the case? Biff -----Original Message----- Hi All, Thank you very much for taking the time to provide various formulas. I need to find /match the LAST time two specific numbers appeared together and Count the number of Rows between the LAST time they appeared together back to their PREVIOUS appearance together. I have a table of numbers that spans nine Columns (A-I) and 400+ Rows (20- 480). I have made the table a Dynamic Range called "Numbers". Each Row contains numbers in ascending order. Is there a formula that can check for two specific numbers Row by Row through the (nine column) Range "Numbers" and Return the Row Count of their LAST appearance together back to their PREVIOUS appearance together, from the Dynamic Range "Numbers"? Columns A-I = Dynamic Range "Numbers" Rows 20-480 Example sample data from Range "Numbers": ROW20 51 58 59 65 69 72 73 76 79 ROW31 50 51 58 72 73 76 79 80 81 ROW50 50 52 60 62 68 69 70 75 76 ROW75 53 54 59 60 62 69 70 72 73 ROW80 50 51 58 59 70 71 72 73 76 ROW83 51 53 65 67 68 69 78 80 81 ROW94 51 52 58 60 61 65 67 72 73 ROW99 50 53 57 62 63 68 70 71 73 Example Criteria: Locate when 72 AND 73 LAST appeared together and Count back to their PREVIOUS appearance together to get the required Count; i.e. the number of Rows in between the LAST appearance and the PREVIOUS appearance. Count from the Row above LAST appearance to the Row before PREVIOUS appearance. Expected Result: 72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95) Count=4 50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32) Count=48 68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51) Count=32 80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32) Count=51 Regards, Sam -- Message posted via http://www.officekb.com . |
Kludgy, but it'll work:
=MAX(IF(findnum1*findnum2<0,ROW(Numbers)))-LARGE(IF (findnum1*findnum2<0,ROW(Numbers)),2)-1 Array-entered, whe findnum1 = COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLUMN (Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS (Numbers))),num1) findnum2 = =COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLUMN (Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS (Numbers))),num2) num1 = cell containing first number num2 = cell containing second number HTH Jason Atlanta, GA -----Original Message----- Hi All, Thank you very much for taking the time to provide various formulas. I need to find /match the LAST time two specific numbers appeared together and Count the number of Rows between the LAST time they appeared together back to their PREVIOUS appearance together. I have a table of numbers that spans nine Columns (A-I) and 400+ Rows (20- 480). I have made the table a Dynamic Range called "Numbers". Each Row contains numbers in ascending order. Is there a formula that can check for two specific numbers Row by Row through the (nine column) Range "Numbers" and Return the Row Count of their LAST appearance together back to their PREVIOUS appearance together, from the Dynamic Range "Numbers"? Columns A-I = Dynamic Range "Numbers" Rows 20-480 Example sample data from Range "Numbers": ROW20 51 58 59 65 69 72 73 76 79 ROW31 50 51 58 72 73 76 79 80 81 ROW50 50 52 60 62 68 69 70 75 76 ROW75 53 54 59 60 62 69 70 72 73 ROW80 50 51 58 59 70 71 72 73 76 ROW83 51 53 65 67 68 69 78 80 81 ROW94 51 52 58 60 61 65 67 72 73 ROW99 50 53 57 62 63 68 70 71 73 Example Criteria: Locate when 72 AND 73 LAST appeared together and Count back to their PREVIOUS appearance together to get the required Count; i.e. the number of Rows in between the LAST appearance and the PREVIOUS appearance. Count from the Row above LAST appearance to the Row before PREVIOUS appearance. Expected Result: 72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95) Count=4 50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32) Count=48 68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51) Count=32 80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32) Count=51 Regards, Sam -- Message posted via http://www.officekb.com . |
Here's one simple set-up to tinker with ..
Assume the source table is in Sheet1, A20:I480 In Sheet1 --------- Put in, say, K20: =IF(AND(ISNUMBER(MATCH(Sheet2!$A$2,A20:I20,0)),ISN UMBER(MATCH(Sheet2!$B$2,A2 0:I20,0))),ROW(),"") Copy K20 down to K480 In Sheet2 --------- Assume the pair of numbers (e.g.: 68,69 or 80,81 etc) will be input into A2:B2 The order of the paired inputs into A2:B2 is immaterial, can be 68,69 or 69,68, for example Put in C2: =IF(OR($A2="",$B2=""),"",LARGE(Sheet1!$K$2:$K$480, COLUMNS($A$1:A1))) Copy C2 across to D2 Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1) For the pair of numbers input into A2:B2 : C2 will return the row number of the last occurrence in Sheet1 D2 will return the row number of the 2nd last occurrence in Sheet1 E2 will return the number of rows in-between the last and the 2nd last occurrence in Sheet1 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sam via OfficeKB.com" wrote in message ... Hi All, Thank you very much for taking the time to provide various formulas. I need to find /match the LAST time two specific numbers appeared together and Count the number of Rows between the LAST time they appeared together back to their PREVIOUS appearance together. I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20- 480). I have made the table a Dynamic Range called "Numbers". Each Row contains numbers in ascending order. Is there a formula that can check for two specific numbers Row by Row through the (nine column) Range "Numbers" and Return the Row Count of their LAST appearance together back to their PREVIOUS appearance together, from the Dynamic Range "Numbers"? Columns A-I = Dynamic Range "Numbers" Rows 20-480 Example sample data from Range "Numbers": ROW20 51 58 59 65 69 72 73 76 79 ROW31 50 51 58 72 73 76 79 80 81 ROW50 50 52 60 62 68 69 70 75 76 ROW75 53 54 59 60 62 69 70 72 73 ROW80 50 51 58 59 70 71 72 73 76 ROW83 51 53 65 67 68 69 78 80 81 ROW94 51 52 58 60 61 65 67 72 73 ROW99 50 53 57 62 63 68 70 71 73 Example Criteria: Locate when 72 AND 73 LAST appeared together and Count back to their PREVIOUS appearance together to get the required Count; i.e. the number of Rows in between the LAST appearance and the PREVIOUS appearance. Count from the Row above LAST appearance to the Row before PREVIOUS appearance. Expected Result: 72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95) Count=4 50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32) Count=48 68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51) Count=32 80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32) Count=51 Regards, Sam -- Message posted via http://www.officekb.com |
If the numbers are always consecutive, as noted in my
reply, this is much easier than all of that! Biff -----Original Message----- Kludgy, but it'll work: =MAX(IF(findnum1*findnum2<0,ROW(Numbers)))-LARGE(IF (findnum1*findnum2<0,ROW(Numbers)),2)-1 Array-entered, whe findnum1 = COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLUM N (Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS (Numbers))),num1) findnum2 = =COUNTIF(INDIRECT(ADDRESS(ROW(Numbers),MIN(COLU MN (Numbers)))&":"&ADDRESS(ROW(Numbers),COLUMNS (Numbers))),num2) num1 = cell containing first number num2 = cell containing second number HTH Jason Atlanta, GA -----Original Message----- Hi All, Thank you very much for taking the time to provide various formulas. I need to find /match the LAST time two specific numbers appeared together and Count the number of Rows between the LAST time they appeared together back to their PREVIOUS appearance together. I have a table of numbers that spans nine Columns (A-I) and 400+ Rows (20- 480). I have made the table a Dynamic Range called "Numbers". Each Row contains numbers in ascending order. Is there a formula that can check for two specific numbers Row by Row through the (nine column) Range "Numbers" and Return the Row Count of their LAST appearance together back to their PREVIOUS appearance together, from the Dynamic Range "Numbers"? Columns A-I = Dynamic Range "Numbers" Rows 20-480 Example sample data from Range "Numbers": ROW20 51 58 59 65 69 72 73 76 79 ROW31 50 51 58 72 73 76 79 80 81 ROW50 50 52 60 62 68 69 70 75 76 ROW75 53 54 59 60 62 69 70 72 73 ROW80 50 51 58 59 70 71 72 73 76 ROW83 51 53 65 67 68 69 78 80 81 ROW94 51 52 58 60 61 65 67 72 73 ROW99 50 53 57 62 63 68 70 71 73 Example Criteria: Locate when 72 AND 73 LAST appeared together and Count back to their PREVIOUS appearance together to get the required Count; i.e. the number of Rows in between the LAST appearance and the PREVIOUS appearance. Count from the Row above LAST appearance to the Row before PREVIOUS appearance. Expected Result: 72 AND 73 LAST Appear=ROW99 PREVIOUS Appear=ROW94(Count From Row98 To Row95) Count=4 50 AND 51 LAST Appear=ROW80 PREVIOUS Appear=ROW31(Count From Row79 To Row32) Count=48 68 AND 69 LAST Appear=ROW83 PREVIOUS Appear=ROW50(Count From Row82 To Row51) Count=32 80 AND 81 LAST Appear=ROW83 PREVIOUS Appear=ROW31(Count From Row82 To Row32) Count=51 Regards, Sam -- Message posted via http://www.officekb.com . . |
Hi Biff,
Sorry, yes it is a typo. Should be 72 AND 73 LAST Appear=ROW94 Previous Appear=ROW80. The numbers will always be consecutive. Regards, Sam -- Message posted via http://www.officekb.com |
Hi!
OK. Assume your table is in the range A20:I480. Use a cell to hold the numbers you're looking for, say A1: A1 = 7273 In a helper column, say column J, in J20 enter this formula and copy down to J480. (or, just double click the fill handle) =IF(ISNUMBER(SEARCH (A$1,A20&B20&C20&D20&E20&F20&G20&H20&I20)),ROW()," ") Then, to find the number of rows between the last instance and the previous instance: =LARGE(J20:J480,1)-LARGE(J20:J480,2)-1 Biff -----Original Message----- Hi Biff, Sorry, yes it is a typo. Should be 72 AND 73 LAST Appear=ROW94 Previous Appear=ROW80. The numbers will always be consecutive. Regards, Sam -- Message posted via http://www.officekb.com . |
For the pair of numbers input into A2:B2 :
C2 will return the row number of the last occurrence in Sheet1 D2 will return the row number of the 2nd last occurrence in Sheet1 E2 will return the number of rows in-between the last and the 2nd last occurrence in Sheet1 The test results (below) seems to reconcile with expected results based on the source data as originally posted, with typo corrected for line: ROW99 50 53 57 62 63 68 70 71 73 to be ROW99 50 53 57 62 63 68 70 72 73 Paired inputs in A2:B2 returns in: C2 - D2 - E2 ----------------------------------------------- 72,73 returns: 99 - 94 - 4 50,51 returns: 80 - 31 - 48 68,69 returns: 83 - 50 - 32 80,81 returns: 83 - 31 - 51 (Paired inputs can be in any order: 73,72 or 51,50 etc) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Hi Biff,
The Dynamic Range "Numbers" is the main source. However, I've had to set up a summary sheet where the consecutive numbers I'm looking for are in two separate Columns on the same Row. Eg: 72 AND 73 in Columns A and B Row 2 respectively. Can your formula using the SEARCH Function accommodate my summary sheet setup. Regards, Sam -- Message posted via http://www.officekb.com |
Hi Jason,
Thank you for assistance. I've entered your Array Formula as suggested keeping the FindNum1 and FindNum2 in separate manageable parts; however, I get a #Num! error - not sure why? I've also input the Formula as one very large Formula but still get the #Num! error. Would appreciate further assitance with using the "Numbers" Dynamic Range. Regards, Sam -- Message posted via http://www.officekb.com |
Hi!
Certainly! You enter the numbers to search for in: Summary A2 = 72 Summary B2 = 73 =IF(ISNUMBER(SEARCH(Summary!A$2&Summary! B$2,A20&B20&C20&D20&E20&F20&G20&H20&I20)),ROW(),"" ) Biff -----Original Message----- Hi Biff, The Dynamic Range "Numbers" is the main source. However, I've had to set up a summary sheet where the consecutive numbers I'm looking for are in two separate Columns on the same Row. Eg: 72 AND 73 in Columns A and B Row 2 respectively. Can your formula using the SEARCH Function accommodate my summary sheet setup. Regards, Sam -- Message posted via http://www.officekb.com . |
Hi Max,
Thanks for your assitance. I actually need the formula to find the information your formula returns but for a complete column of paired numbers rather than just a single Row. The paired numbers are housed on a summary sheet in Columns A and B starting from Row 2. Is it possible to provide such a Formula that uses the original Dynamic Range "Numbers" rather than referencing the source data using the A1 reference style. The numbers to find will be referenced as A2 and B2, A3 and B3, A4 and B4 etc., down the two columns. Regards, Sam -- Message posted via http://www.officekb.com |
Hi Biff,
Correction to my previous Post to you. The Dynamic Range "Numbers" is the main source. However, I've had to set up a summary sheet where the consecutive numbers I'm looking for are in two separate Columns on the same Row. Eg: 72 AND 73 in Columns A and B Row 2 respectively. However, I also have other paired numbers going all the way down column A and B Row3, Row4, Row5 etc. where I need the same Row Count information. Can your formula using the SEARCH Function accommodate my summary sheet setup. Regards, Sam -- Message posted via http://www.officekb.com |
... for a complete column of paired
numbers rather than just a single Row. The paired numbers are housed on a summary sheet in Columns A and B starting from Row 2. Was afraid you'd say that <g Ok, we could try this revised set-up which uses 3, 2 variable data tables to compute the last row number, the 2nd last row number and the difference between the last and 2nd last row number for the paired values in Sheet2, cols A and B, in A2:B2 down There's no change to the set-up in Sheet1 with the formula in K20:K480 In Sheet2 --------- Put in C2 (revised slightly): =IF(OR($A2="",$B2="",$A2=$B2),"",IF(ISERROR(LARGE( Sheet1!$K$2:$K$480,COLUMNS ($A$1:A1))),"",LARGE(Sheet1!$K$2:$K$480,COLUMNS($A $1:A1)))) Copy C2 across to D2 Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1) (no change) The above 3 formulas in C2:E2 will be utilized in setting-up 3, 2 variable data tables, the set-ups of which are described below, The 3 data tables are identical in structure, except for the link formula in the top left corner cell which will point to C2, D2 and E2. I chose to use the numbers 50-81 which appear to be the range of numbers within the source table in Sheet1 for listing the horizontal "x" and the vertical "y" values in the 3 data tables (Adapt the set up accordingly to suit your actual case) Data Table #1 ------------- Put in G1: =C2 Number across in H1:AM1, the numbers: 50, 51, 52, 53 ... 81 (horiz x values) Number down in G2:G33, the numbers: 50, 51, 52, 53 ... 81 (vertical y values) Select G1:AM33 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H2:AM33 will compute the last row numbers at the x and y intersections Data Table #2 ------------- Put in G35: =D2 Number across in H35:AM35, the numbers: 50, 51, 52, 53 ... 81 (horiz x values) Number down in G36:G67, the numbers: 50, 51, 52, 53 ... 81 (vertical y values) Select G35:AM67 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H36:AM67 will compute the 2nd last row numbers at the x and y intersections Data Table #3 ------------- Put in G69: =E2 Number across in H69:AM69, the numbers: 50, 51, 52, 53 ... 81 (horiz x values) Number down in G70:G101, the numbers: 50, 51, 52, 53 ... 81 (vertical y values) Select G69:AM101 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H70:AM101 will compute the difference between the last and the 2nd last row numbers at the x and y intersections And with the 3 data tables above in place, to wrap up, we'll just need to Put in C3: =OFFSET($G$1,MATCH(A3,$G$2:$G$33,0),MATCH(B3,$H$1: $AM$1,0)) Put in D3: =OFFSET($G$35,MATCH(A3,$G$36:$G$67,0),MATCH(B3,$H$ 35:$AM$35,0)) Put in E3: =OFFSET($G$69,MATCH(A3,$G$70:$G$101,0),MATCH(B3,$H $69:$AM$69,0)) Then select C3:E3, and fill down as needed Cols C to E will return (if found) the corresponding values of the last row number, the 2nd last row number and the difference between the last and 2nd last row numbers for the paired values entered in cols A and B Adapt to suit .. Note: You might want to set the calc mode to "Automatic except tables" Click Tools Options Calculation tab Check "Automatic except tables" OK (Remember to click F9 to recalc the data tables if ncess., e.g., if you redo/change the x and y values, etc) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sam via OfficeKB.com" wrote in message ... Hi Max, Thanks for your assitance. I actually need the formula to find the information your formula returns but for a complete column of paired numbers rather than just a single Row. The paired numbers are housed on a summary sheet in Columns A and B starting from Row 2. Is it possible to provide such a Formula that uses the original Dynamic Range "Numbers" rather than referencing the source data using the A1 reference style. The numbers to find will be referenced as A2 and B2, A3 and B3, A4 and B4 etc., down the two columns. Regards, Sam -- Message posted via http://www.officekb.com |
Hi!
Sam wrote: ... for a complete column of paired numbers rather than just a single Row. The paired numbers are housed on a summary sheet in Columns A and B starting from Row 2. And Max responded: Was afraid you'd say that <g Biff says: Sam must be a pitcher on a baseball team. He's got a mean=20 curve ball! Here's another solution! On the sheet where the number table is: In K20 enter this formula and copy down: =3DA20&B20&C20&D20&E20&F20&G20&H20&I20 In L20 enter this formula and copy down: =3DROW() On the Summary sheet: In C2 enter this formula and copy down: =3DA2&B2 Now, calculate the the number of rows between the last=20 instance and the next to last instance. In D2 enter this formula with the key combo of=20 CTRL,SHIFT,ENTER: =3DINDEX(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=20 in rows. You'll need to tweak all the references to suit. Biff -----Original Message----- ... for a complete column of paired numbers rather than just a single Row. The paired numbers are housed on a summary sheet in Columns A and B starting from Row 2. Was afraid you'd say that <g Ok, we could try this=20 revised set-up which uses 3, 2 variable data tables to compute the last row=20 number, the 2nd last row number and the difference between the last and 2nd=20 last row number for the paired values in Sheet2, cols A and B, in A2:B2 down There's no change to the set-up in Sheet1 with the=20 formula in K20:K480 In Sheet2 --------- Put in C2 (revised slightly): =3DIF(OR($A2=3D"",$B2=3D"",$A2=3D$B2),"",IF(ISERR OR(LARGE(Sheet1! $K$2:$K$480,COLUMNS ($A$1:A1))),"",LARGE(Sheet1!$K$2:$K$480,COLUMNS ($A$1:A1)))) Copy C2 across to D2 Put in E2: =3DIF(OR(C2=3D"",D2=3D""),"",(C2-D2)-1) (no change) The above 3 formulas in C2:E2 will be utilized in setting- up 3, 2 variable data tables, the set-ups of which are described below,=20 The 3 data tables are identical in structure, except for the link formula in=20 the top left corner cell which will point to C2, D2 and E2. I chose to use=20 the numbers 50-81 which appear to be the range of numbers within the source=20 table in Sheet1 for listing the horizontal "x" and the vertical "y"=20 values in the 3 data tables (Adapt the set up accordingly to suit your actual=20 case) Data Table #1 ------------- Put in G1: =3DC2 Number across in H1:AM1, the numbers: 50, 51, 52, 53 ...=20 81 (horiz x values) Number down in G2:G33, the numbers: 50, 51, 52, 53 ... 81=20 (vertical y values) Select G1:AM33 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H2:AM33 will compute the last row numbers at the=20 x and y intersections Data Table #2 ------------- Put in G35: =3DD2 Number across in H35:AM35, the numbers: 50, 51, 52,=20 53 ... 81 (horiz x values) Number down in G36:G67, the numbers: 50, 51, 52, 53 ...=20 81 (vertical y values) Select G35:AM67 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H36:AM67 will compute the 2nd last row numbers=20 at the x and y intersections Data Table #3 ------------- Put in G69: =3DE2 Number across in H69:AM69, the numbers: 50, 51, 52,=20 53 ... 81 (horiz x values) Number down in G70:G101, the numbers: 50, 51, 52, 53 ...=20 81 (vertical y values) Select G69:AM101 Click Data Table Enter in the boxes For Row input cell: A2 For Col input cell: B2 Click OK The grid H70:AM101 will compute the difference between=20 the last and the 2nd last row numbers at the x and y intersections And with the 3 data tables above in place, to wrap up,=20 we'll just need to Put in C3: =3DOFFSET($G$1,MATCH(A3,$G$2:$G$33,0),MATCH (B3,$H$1:$AM$1,0)) Put in D3: =3DOFFSET($G$35,MATCH(A3,$G$36:$G$67,0),MATCH (B3,$H$35:$AM$35,0)) Put in E3: =3DOFFSET($G$69,MATCH(A3,$G$70:$G$101,0),MATCH (B3,$H$69:$AM$69,0)) Then select C3:E3, and fill down as needed Cols C to E will return (if found) the corresponding=20 values of the last row number, the 2nd last row number and the difference=20 between the last and 2nd last row numbers for the paired values entered in cols A=20 and B Adapt to suit .. Note: You might want to set the calc mode to "Automatic=20 except tables" Click Tools Options Calculation tab =20 Check "Automatic except tables" OK (Remember to click F9 to recalc the data tables if=20 ncess., e.g., if you redo/change the x and y values, etc) -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom ---- "Sam via OfficeKB.com" wrote in=20 message m... Hi Max, Thanks for your assitance. I actually need the formula=20 to find the information your formula returns but for a complete=20 column of paired numbers rather than just a single Row. The paired numbers are housed on a summary sheet in=20 Columns A and B starting from Row 2. Is it possible to provide such a Formula that uses the=20 original Dynamic Range "Numbers" rather than referencing the source data=20 using the A1 reference style. The numbers to find will be=20 referenced as A2 and B2, A3 and B3, A4 and B4 etc., down the two columns. Regards, Sam -- Message posted via http://www.officekb.com . |
Hi Biff,
Thank you all for ongoing help. What's this symbol in front of the &B2 and what does it do - ?&B2? Regards, Sam -- Message posted via http://www.officekb.com |
What's this symbol in front of the &B2 and what does it do -
?&B2? Just a few words on Biff's behalf <g, but do hang around awhile for his response Using the ampersand "&" is equivalent to using the function CONCATENATE, except that it's much shorter to type <g Example: Putting in C2: = A2&" "&B2 will return "Buck Rogers" if A2 contains: "Buck", B2 contains: "Rogers" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Sam,
Were you able to get the suggested set-up working ? Or did you give up halfway <g ? If you're interested, I could send you a working sample book via private email. Just post a "readable" email add. in response here. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
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 |
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 |
Perhaps just email me at either of the 2 addresses below (both valid):
demechanik <atyahoo<dotcom xdemechanik <atyahoo<dotcom -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "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 <atyahoo<dotcom ---- "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 <atyahoo<dotcom ---- |
Hi Max,
Email sent to your account - demechanik <atyahoo<dotcom 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 <atyahoo<dotcom ---- |
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 <atyahoo<dotcom ---- |
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 <atyahoo<dotcom ---- |
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 <atyahoo<dotcom ---- |
Hi Max & Biff,
Thank you for ongoing assistance. Thanks for email attachment - will try amendments and post back in Newsgroup. Regards, Sam -- Message posted via http://www.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 -- Message posted via http://www.officekb.com |
You're welcome, Sam !
Glad to hear you got what you wanted Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sam via OfficeKB.com" wrote in message ... 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 -- Message posted via http://www.officekb.com |
.. 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 .. 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com