Combining arrays

Part of the Macaulay spreasheet presented in recent posts required the formation of a list of points along a beam where the functions needed to be evaluated.  These are: The support points, the changes of cross section, and the output points specified by the user.  These points are in three separate arrays which must be combined into one, maintaining the correct order from left to right, and without duplication of any point.

I have now converted the function that performs the combination so that it can also be used as a User Defined Function (UDF) in a worksheet.  The result (including full open source code) may be downloaded from:

The function works with single column or multiple column arrays, exmples of which are shown in the screenshots below:

CombineArray documetation and input ranges

Output for single and twin column arrays

Note that CombineArray works as an array function, and must be entered with the correct procedure:

  • Enter the function
  • Select the entire output range, as shown shaded blue above
  • Press F2 to enter Edit mode
  • Press Ctrl-Shift-Enter to enter as an array function.
This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

7 Responses to Combining arrays

  1. Hui... says:

    You can combine two single column Arrays using this array/named formula:

    Thanx Sam & Koitaki @ ExcelHero Academy


    • Marko Limbek says:

      Hi Hui,
      That is great formula but it doesn’t work in my case, where List1 and List2 are not fixed values in ranges cells but List1 and List2 are themselves created with complicated formulas like =IFERROR(INDEX(ROW(3:10)-ROW(3:10); ROWS(C$1:$C3)); IFERROR(INDEX(ROW(4:5)-ROW(3:4); ROWS(C$1:$C3)-ROWS(ROW(3:10)-ROW(3:10))); “”)). This is a complicated formula for a single cell.
      Do you know a solution?


  2. dougaj4 says:

    Hi Hui
    Neat formula, but it does something different to the UDF. It combines two nx1 arrays into an nx2 array, whereas the UDF outputs an array with the same number of columns as the widest input array, with no duplicate values in column 1.


  3. lhm says:

    However if you wanted to do this without code you might be able to make use of Hui’s suggestion, or variant of it. For example by using the following setup (* indicates the formula needs to be array entered in E18 and filled down):

    D18 =MIN(A$9:A$15,D$9:D$13)
    D18:D27 =LARGE((A$9:A$15,D$9:D$13),RANK(D18,(A$9:A$15,D$9:D$13))-1)
    E18:E27* =MIN(IFERROR(IF({1,0},B$9:B$15,E$9:E$13)/(IF({1,0},A$9:A$15,D$9:D$13)=D18),""))


  4. Pingback: Comparing floating point numbers | Newton Excel Bach, not (just) an Excel Blog

  5. dougaj4 says:

    Thanks Lori.

    See today’s post (7 Jan 2012).


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s