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:
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.
Newton Excel Bach, not (just) an Excel Blog 
You can combine two single column Arrays using this array/named formula:
=CHOOSE({1;2},TRANSPOSE(List1),TRANSPOSE(List2))
Thanx Sam & Koitaki @ ExcelHero Academy
LikeLike
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
LikeLike
Marko – can you give more details about what you are wanting to do? Maybe there is a simpler way.
LikeLike
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.
LikeLike
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),""))
LikeLike
Pingback: Comparing floating point numbers | Newton Excel Bach, not (just) an Excel Blog
Thanks Lori.
See today’s post (7 Jan 2012).
LikeLike