[Solved] Merge data from multiple colmns into one columns

Discuss the spreadsheet application
Post Reply
alaryk
Posts: 4
Joined: Sun Nov 30, 2014 4:21 pm

[Solved] Merge data from multiple colmns into one columns

Post by alaryk »

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

Image
Last edited by MrProgrammer on Tue Sep 01, 2020 9:07 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice4.0.1
User avatar
Lupp
Volunteer
Posts: 3698
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: merge data from multiple colmns into one columns

Post by Lupp »

... 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.
Attachments
ooo73841CollectColumnsIntoOne001.ods
(40.94 KiB) Downloaded 2491 times
On Windows 10: LibreOffice 25.2.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
alaryk
Posts: 4
Joined: Sun Nov 30, 2014 4:21 pm

Re: Merge data from multiple colmns into one columns

Post by alaryk »

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 :bravo: 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/
OpenOffice4.0.1
coray80
Volunteer
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Merge data from multiple colmns into one columns

Post by coray80 »

This is a Ken Johnson formula!
Attachments
alaryk.ods
(9.32 KiB) Downloaded 812 times
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
User avatar
Lupp
Volunteer
Posts: 3698
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Merge data from multiple colmns into one columns

Post by Lupp »

@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
alaryk
Posts: 4
Joined: Sun Nov 30, 2014 4:21 pm

Re: Merge data from multiple colmns into one columns

Post by alaryk »

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 :super:
OpenOffice4.0.1
User avatar
MrProgrammer
Moderator
Posts: 5297
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge data from multiple columns into one column

Post by MrProgrammer »

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.
Attachments
201412011517.ods
(11.27 KiB) Downloaded 813 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.6, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply