@alaryk: You linked in a page by "get-digital-help.com". What's so great about a formula published by someone for "The Marvelous Excel"? Did you try to understand it? It might be wrong.
That's nothing to do with "Superior Excel" or some other shit. If the formula was correct it worked wit LibO Calc, at least from V4.1. And the only issue for what it might not have worked with the older versions is the unnecessary use it makes of IFERROR().
Let's discuss it. The first version should read (as an array formula):
"Excel 2007 array formula in C2:"
{=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))}
1) The formula
need not be entered as an array formula. It simply does not contain a subexpression needing to be treated as an array-expression in iterative mode.
(Obscuration because "array-formulae" have a special flair?)
2) The subexpression "ROWS(C1:$C$1)" occurring twice shall simply introduce
the number (index) of the element of the (merged) output column. This meaning: The first cell containing the formula shall get returned the value of 1, the second one the value of 2, and so on.
If we want to do that without a helper calculating this number (which might be reasonable) we should do it in a way speaking out its idea. This might be "ROW(C1) - ROW(C$1)+1", e.g. but at least it must be correct and downright.
What is reading "ROWS(C1:$C$1)" in the first cell will be "ROWS(C2:$C$1)" in the second cell to which it gets filled down, then "ROWS(C3:$C$1)" and so on. Such "upward ranges" are allowed for by ODF specifications, too, and they are, alas, presently not always evaluated correctly in LibO Calc. To use them in this case where the clearer ROWS(C$1:C1) would work, is another obscuration.
3) The formula requires defining the lists to merge as named ranges which might be a useless overhead in this case, in specific because there is not in the least introduced an advantage by it. This would require defining the names meaning OFFSET() expressions, which on its turn would require counting the actual entries.
4) The formula is
specialised to the case of exactly two lists to merge. How should one enhance it to have 5 columns of variable length merged?
5) We are told to enter the formula into C2 and then fill it down as far as needed. Actually we may start in any cell with exactly this formula.
Putting away the (in this case) ineffective IFERROR() and replacing it by a clear logical test we may rectify the
second suggestion ("Older Excel-versions") and get:
Code: Select all
=IF(ROWS($C$1:C1)>ROWS(list1); IF( ROWS($C$1:C1)>ROWS(list1)+ROWS(list2); ""; INDEX(list2; ROWS($C$1:C1)-ROWS(list1))); INDEX(list1; ROWS($C$1:C1)))
This version will work also with very old versions of LibO and of AOO as well.
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München