Results 1 to 6 of 6
  1. #1
    New Lounger
    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.

    --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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

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

    zeddy
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 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
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •