[Solved] Combining multiple columns into Column A

Discuss the spreadsheet application

[Solved] Combining multiple columns into Column A

Postby Xeno3 » Thu Mar 08, 2012 8:24 am

okay yup I've seen multiple posts on "how to combine columns" when ppl are really asking on how to merge them together, now how i want to combine is to take all the information from columns A-WL and make a huge list on Column A for example

Before
Code: Select all   Expand viewCollapse view
    A         B           C       
1 Paul      Steve       Polly
2 Frank     Laura       Mack
3 Batty     Guy         Grace
4 Joe       Vince       Mary
5 Shaun     Sheldon     Sally


After
Code: Select all   Expand viewCollapse view
     A         B             C       
1  Paul             
2  Frank         
3  Batty             
4  Joe             
5  Shaun     
6  Steve
7  Laura
8  Guy
9  Vince
10 Sheldon
11 Polly
12 Mack
13 Grace
14 Mary
15 Sally
16
17
18

without having to cut and paste, preferably a macro or if someone has something simpler in mind I would be very greatful

Thanks in advance
Last edited by Xeno3 on Thu Mar 08, 2012 1:29 pm, edited 3 times in total.
OpenOffice 3.3.0 on Windows XP
Xeno3
 
Posts: 7
Joined: Thu Mar 08, 2012 8:08 am

Re: [Issue] Combining multiple columns into Column A

Postby RoryOF » Thu Mar 08, 2012 8:56 am

Please don't multipost; this doesn't speed your chances of a reply and leads to fragmentation of effort. This topic is best in the Calc forum and I will delete your other post in Macros and UNO API

RoryOF, Moderator.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31236
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Issue] Combining multiple columns into Column A

Postby Xeno3 » Thu Mar 08, 2012 9:08 am

oh sorry about that just figured that it was kind of a two part area as a macro could easily get this done, thank you though for letting me know
OpenOffice 3.3.0 on Windows XP
Xeno3
 
Posts: 7
Joined: Thu Mar 08, 2012 8:08 am

Re: [Issue] Combining multiple columns into Column A

Postby Villeroy » Thu Mar 08, 2012 12:27 pm

The following formula works with all Excelish spreadsheet programs:
Code: Select all   Expand viewCollapse view
=INDEX(Data;MOD(ROW()-1;ROWS(Data))+1;INT((ROW()-1)/ROWS(Data))+1)

"Data" is the named range or absolute reference to your multi-column range.
Copy this down from A1 until A(rows*columns) or until you get error 502.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28434
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combining multiple columns into Column A

Postby Xeno3 » Thu Mar 08, 2012 1:28 pm

sweet jesus that line of code combined with the fill down option just may be what i'm looking for

Edit
well not exactly the fill down code ( as it would crash for some odd reason) but setting the selection to A1:A346200 and then copy pasting from time to time in a note pad is what did the trick

Thanks Vileroy problem Solved
OpenOffice 3.3.0 on Windows XP
Xeno3
 
Posts: 7
Joined: Thu Mar 08, 2012 8:08 am

Re: [Solved] Combining multiple columns into Column A

Postby RCMD » Mon Sep 23, 2013 4:11 am

This piece of code works well but is there any way to have it skip blank cells. I feel like I should be able to use ISBLANK or IF(A1<1;...) to make this happen but I just don't see the solution. Can anyone help?
Thanks in advance.
OpenOffice 4.0.0 on XP Pro and MacOs X
RCMD
 
Posts: 1
Joined: Mon Sep 23, 2013 4:02 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 7 guests