Hi there!
Assume we have defined multiple data ranges in Calc document. Data sources are LO Base queries against multiple Base databases, all ranges have identical structure - column headers, data types and so on, see image attached. Is there a pure Calc way to combine these multiple ranges into one new range? I have Macro and SQL based solutions for it, but I am just curious, if there is a Calc based alternative for SQL Union operator’s functionality.
What do you want to do with the combined range? For example, to sum them, specify all three ranges like this: =SUM(B3:B6;D3:D6;F3:F6). Or do you want to view the results in a list, with no further calculations?
Hi Jim!
Thanks a lot for your comment. Such combined range would be the final result with no further operations applied. So it would be just a list with data.
The following is an overview of what can be done with Calc functions.
If you have an SQL solution, then that might be better than these solutions, since databases do this naturally.
The union operator in Calc is a tilde (~), as explained here. This operator can be used in the INDEX function to merge two or more ranges. The following formula was adapted from [Solved] Merging cell ranges in array formula (View topic) • Apache OpenOffice Community Forum. Enter it as an array formula.
=INDEX((B3:B6~D3:D6);{1;2;3;4;1;2;3;4};{1;1;1;1;1;1;1;1};{1;1;1;1;2;2;2;2})
However, this approach is of limited use because currently, there does not seem to be any way of generating the values for the arrays dynamically. For example, the formula below produces an error because ~ does not work in the context of the second parameter to INDEX.
=INDEX((B3:B6~D3:D6);(ROW(B3:B6)~ROW(D3:D6));{1;1;1;1;1;1;1;1};{1;1;1;1;2;2;2;2})
So, the preferred way of merging ranges is to use OFFSET instead. This requires several helper columns to determine the parameters for OFFSET based on the source columns.
A good example of the OFFSET approach is at [Solved] Merge data from multiple colmns into one columns (View topic) • Apache OpenOffice Community Forum. Here is a copy of the file, in case something happens to the original link: 201412011517.ods
Any single-formula solution would in addition require to be entered for array-evaluation and to calculate and lock its output range when called the first time. This range would be fix subsequently. Dynamic changes in the sizes of the primary ranges would not apply. A solution with helpers per row is likely to require a huge number of formulae, and bloat the file size making save/load operations slow…
Directly resorting to SQL or “macro” solution better for the purpose.
Dear @jimk and and @Lupp, thanks a lot for your answers and comments. I have seen, downloaded and tested @Lupp solution from openoffice.org forum previously - it’s smart and took me some time to figure out its logic. And unfortunately currently it seems the only way dealing with the task provided. Before opening this tread, I was pretty sure that there should be some simple joining syntax or new data range definition I was not aware of. Like Test1~Test2~Test3 or similar.
But it’s just fine, it is just my curiosity to find easier and more elegant way of doing things in Calc. SQL way is the best fit in this case, as @jimk mentioned, it is natural task for SQL, not Calc. I am ok with it. Once again, thanks a lot for explaining things and for your time.
=INDEX((B3:B6~D3:D6);...) should not work because “A reference list is not allowed inside an array expression.”. Operators in LibreOffice Calc - LibreOffice Help
It works though. Even more, if I replace ~ with ; and enter an array formula, LO changes semicolon back to tilde automatically. And if you use ~, you even do not need parentheses around Reference argument in Index function. Maybe this is because Index allows multiple ranges? Or array expression means {a,b,c,d…}, not an array formulae?
Quoting @SM_Riga: “… but I am just curious, if there is a Calc based alternative for SQL Union operator’s functionality.”
I was also curious and made a demo similar to what the OQ presented unfortunately only as an image. (Always bad if not the question is specificallly about view issues.)
There was a total 95 rows in three groups of 3 columns each and the solution was designed to allow for a maximum of 1000 rows per group. (Using formulae you always need to specify such maximums.)
The solution based on standard functions was made using 3 helper columns and a few additional helper cells. Its final file size was about 200 KiB.
A solution with a slightly adapted custom function from my toolbox under array-evaluation made a file of 24 KiB.
Of course it may be markedly slower if much more than 3000 rows of output are prepared. The file size would (next to) only grow for the data, not for the output range.
Edit1 regarding the comment below:
In case there is interest I attach this above mentioned demo containing the custom function I used.
(No idea for what reason the file size went up to about 40 Kib. I changed next to nothing.)
To pass and to evaluate an unknown number of parameters (for the same purpose) is a tricky thing and will only be possible at all if an upper limit for that number is assured.
Please note: The demo will not correctly work in AOO.
I was also considering to write user defined function for this task, but my knowledge of Basic is really basic 
And I stuck in writing VB command to join multiple arrays together. I managed to pass fixed number of ranges as function’s arguments and that’s all by now, not even passing array of unknown number of ranges to function. But hopefully, I will try to learn this later 
Amazing Work @Lupp.
There is no easy way to STACK UP the data and “Data–>Consolidate” SUCKS.
This is the main reason I had to move to Googlesheets where QUERY function does the same job almost effortlessly.
I loved your approach to workaround this in Libreoffice Calc. This formula of yours ( COLLECTFROMRANGES ) should be made standard into libreoffice calc.