Results 1 to 6 of 6
Thread: UDF for sub array

20120510, 11:32 #1
 Join Date
 May 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
UDF for sub array
Hi all. Begging for help.
Here is the data.
abcdefgh
JohnAlexAlice
JohnAlicea1q4w7
 b2a5s8
 c3z6 x9
I disparately need a udf, which will check what names in A2:B2, find them in C1:H1 and create sub array leaving only those columns corresponding to the right names. In this example the result would be (if'd be displayed):
abcd
JohnAlice
a1w 7
b2s 8
c3 x 9
the result of this udf will be used only inside functions like index, if, match and so on.
I'm really not familiar with VBA, but it seems to be simple. I truly help some one can help here. Thanks in advance.

20120510, 13:31 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,188
 Thanks
 152
 Thanked 591 Times in 561 Posts
Hi
See attached file.
You don't need a UDF.
You could just use formulas as shown in the attached file.
zeddy

20120510, 14:07 #3
 Join Date
 May 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hi, thanks for the replay and efforts. Now look. I need this procedure to be done app 500 times, every time with different names, but the same table. I cannot afford to myself to display it, so it should be one heavy array formula (which is impossible i believe) or udf. You used 3 functions, 2 of which could be merged into 1, but 3rd function (where you add column+1) is the problem. Meaning if i need this subarray, using your way I'll have to display it. Unacceptable
But again thank you, and you gave me some inspiration/new look on this problem  so double thanks

20120511, 04:04 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,188
 Thanks
 152
 Thanked 591 Times in 561 Posts
Hi
See attached file for another method.
This uses a formula to return an array:
=OFFSET($A$1,0,MATCH(A2,1:1,FALSE)1,4,2)
The value in A2 is the name you are looking for in row 1 (exact match) i.e. the (MATCH(A2,1:1,FALSE) bit.
For example, this will return 9 for Bob, i.e. Bob is in column 9.
The offset function then says, starting from the cell I specify (in this case cell $A$1):
Go down 0 rows, move to the right 8 columns (i.e. 9  1),
..then return a block which is 4 rows deep by 2 cols wide.
If you copy the formula above into any cell, then highlight the entire formula in the formula bar and then press [F9], you will see the array result displayed in the formula bar.
You could use a similar formula for your second name.
Does this help?
zeddy

20120511, 04:22 #5
 Join Date
 May 2012
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you for ur time. It's nice try, but still it cant serve the need. I need one formula that produces sub array (which is function of names in A2:B2),to put it as a range into large array function. This large array function will produce an array of some values out of the sub array and this array of values will be used as variable in regression. There will be 500 such regressions. That's why it's not the option to display the sub arrays. In the case of using 2 or more formulas for extracting an sub array, sub array has to be displayed. But again thank you for ur efforts. I've tried all possible combinations of offset, choose, if, index, match, small, large, row,column, transpose, lookups and others. Nothing help. My conclusion  Excel cant do this (without VBA) ( It's sad to state it.

20120511, 04:40 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,188
 Thanks
 152
 Thanked 591 Times in 561 Posts
Hi
You are correct. To get the actual array you need, you will have to use VBA.
I was trying to show you how you could get one half of your required array using existing Excel formulas.
It should be relatively easy to 'combine' the two parts into a single array within VBA.
zeddy