Apache OpenOffice Community Forum

Hello, Needing help on how to merge data from multiple columns into one columns as seen in the picture
Thank you very much

... Needing help on how to merge data from multiple columns into one ...

You will need "helpers", too.
The entries in the source columns need counting. For the rows of the result you will have to calculate from where (column and row) to get the appropriate entry. Then OFFSET() will do the rest.
See attached example.

Wow your solution is great, kinda blew me away on how you tackle it, and i really appreciate the file sample and the color legends Thanks very much Lupp
I just stumbled upon a simpler formula but the problem is its in excel formula, I'm not sure if its possible to convert it to openoffice formula, tried converting it but still having a hard time understanding how, anyone knows how? Thanks
http://www.get-digital-help.com/2009/06 ... -in-excel/

This is a Ken Johnson formula!

@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: This version will work also with very old versions of LibO and of AOO as well.

Thank you very much for all your efforts in providing a sample file and explanation @Lupp & @coray80, Its still a long long way for me using calc specially since what MSword had become, changing all the classic keyboard shortcuts to chaotic mouse clicks in excel2007.

@Lupp don't get me wrong, sure excel have its pros and cons, and so does calc but i love calc, im just saying i stumbled upon a formula that seems more simple than yours, but it is not necessarily better than yours. Thank you tho for the detailed explanation, i really appreciate it.

Thank you very much guys for taking the time to help me out, especially sir ken johnson

Merging columns can be done with simple formulas if none of the columns are empty, as shown in the NonEmpty sheet of my attachment. When columns can be empty, additional formulas are necessary, as shown in the Empty sheet. It's not clear from your example, but I have assumed that a list ends when an empty cell is encountered. If that's not the case, and empty cells can occur within a list, even more complicated formulas would be needed.

Lupp wrote:That's nothing to do with "Superior Excel" or some other shit. … Let's discuss it. …

+1, or as I would say on FaceBook, LOL.

Edit: For the benefit of non-FB or non-native English speakers, LOL means that I thought your post was amusing, Lupp.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.