Combine Arrays Function: COMBINE(Array1, Array2 , ...)
A functions to combine arrays and return a single array. For example, I have a spreadsheet that contains a list of schools and a list of theatres. Some schools also have theatres, so I would like to combine a subset of the schools with the theatres to produce an array of venues that then be used as a validation list. Currently I use the new FILTER() and SORT() functions to create 2 arrays and then use an awkward and complex array function with helper functions (based on MID()) to combine them. Would be great if I could just e.g. SORT(COMBINE(A3#,B3#))
In answer to anonymous, see attached file. I have 3 tables (SAMPLE_1, 2 and 3 on tabs T1,2 and 3 respectively). On Sheet1 ARRAY1,2 and 3 use the UNIQUE() function to give unique values of each of the SAMPLE tables. In order to combine these three arrays into a single list, in cell F4 I use this nightmare:
=IFERROR(INDEX($B$4#, ROWS(F3:$F$3)), IFERROR(INDEX($C$4#, ROWS(F3:$F$3)-ROWS($B$4#)), IFERROR(INDEX($D$4#, ROWS(F3:$F$3)-ROWS($B$4#)-ROWS($C$4#)), "")))
to combine all the arrays. This has to be copied down to each of the cells in column F, and you hope that as data is added to the three SAMPLE tables that you have copied down into column F for enough rows to cover all the entries. (It's not an array function now, I found a way of doing it without using array functions and MID())
Then I use SORT() and FILTER() in cell H4 to produce the final result.
If I had a COMBINE() function to combine arrays, I could simple replace all of the workings on Sheet1 with the following in H4:
I would REALLY APPRIECIATE THIS!!!!
What is the "complex array function" that you now use?