## 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: http://www.interactiveds.com.au/software/CombineArray.xls

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

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:
=CHOOSE({1;2},TRANSPOSE(List1),TRANSPOSE(List2))

Thanx Sam & Koitaki @ ExcelHero Academy

Like

• 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?
Best,
Marko

Like

• dougaj4 says:

Marko – can you give more details about what you are wanting to do? Maybe there is a simpler way.

Like

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.

Like

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),""))```

Like

4. dougaj4 says:

Thanks Lori.

See today’s post (7 Jan 2012).

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.