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 |
#11
|
|||
|
|||
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 . |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
... 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 atyahoodotcom ---- "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 |
#15
|
|||
|
|||
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 atyahoodotcom ---- "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 . |
#16
|
|||
|
|||
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 |
#17
|
|||
|
|||
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 atyahoodotcom ---- |
#18
|
|||
|
|||
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 atyahoodotcom ---- |
#19
|
|||
|
|||
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 |
#20
|
|||
|
|||
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 |
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 |