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
.