UDF for sub array

[ystrica]

Hi all. Begging for help.

Here is the data.

--a----b---------c----d-----e-----f-----g----h-
----------------John------Alex---------Alice
John-Alice-------a----1-----q-----4----w----7
----------------- b----2-----a-----5----s----8
----------------- c----3-----z-----6---- x----9

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):

--a----b---c----d----
John------Alice
a-----1----w---- 7
b-----2----s---- 8
c-----3---- 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.

[zeddy]

Hi

See attached file.
You don't need a UDF.
You could just use formulas as shown in the attached file.

zeddy

[ystrica]

Hi

See attached file.
You don't need a UDF.
You could just use formulas as shown in the attached file.

zeddy

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

[zeddy]

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

[ystrica]

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

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.

[zeddy]

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