# How to merge the columns into one by their rows?

1. ## How to merge the columns into one by their rows?

Hi,

How to merge several columns into one column by placing the aligned rows from each column below each other. For example, the below columns A, B, and C...

A B C
book1 table1 door1
book2 table2 door2
book3 table3 door3
book4 table4 door4

... must result into the following single column:

book1
table1
door1
book2
table2
door2
book3
table3
door3
book4
table4
door4

Many thanks!
Gabriele

2. ## Re: How to merge the columns into one by their rows?

Try this Macro

``Please Login or Register  to view this content.``

3. ## Re: How to merge the columns into one by their rows?

Many thanks for the solution! It does work.

Best,
Gabriele

4. ## Re: How to merge the columns into one by their rows?

Hi Gabriele,

If you had a newer version of Excel, and installed the*free Excel tool called "Power Query", you could do this problem using the "UnPivot" columns tool. You*would simply select all 3 columns and click on UnPivot.

5. ## Re: How to merge the columns into one by their rows?

Here's a formula method...

Data Range
 A B C D E 2 book1 table1 door1 ------ book1 3 book2 table2 door2 table1 4 book3 table3 door3 door1 5 book4 table4 door4 book2 6 table2 7 door2 8 book3 9 table3 10 door3 11 book4 12 table4 13 book4 14

This formula entered in E2:

=IFERROR(INDEX(A\$2:C\$5,CEILING(ROWS(E\$2:E2)/3,1),3-MOD(ROWS(E2:E\$12),3)),"")

Copy down until you get blanks.

6. ## Re: How to merge the columns into one by their rows?

Hi Tony,

many thanks for another method. It does work too!

Best,
G

7. ## Re: How to merge the columns into one by their rows?

Originally Posted by MarvinP
Hi Gabriele,

If you had a newer version of Excel, and installed the*free Excel tool called "Power Query", you could do this problem using the "UnPivot" columns tool. You*would simply select all 3 columns and click on UnPivot.

Hi MarvinP,

Unfortunately I work with an older version of Excel. But thanks for advice. I did learn something new. The Power Query tool looks such a great addition to Excel!

Best,
Gabriele

8. ## Re: How to merge the columns into one by their rows?

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1