[Solved] Combining multiple columns into Column A

[Xeno3]

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

    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

     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

[RoryOF]

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.

[Xeno3]

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

[Villeroy]

The following formula works with all Excelish spreadsheet programs:

Code: Select all

=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.

[Xeno3]

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

[RCMD]

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.