+ Reply to Thread
Results 1 to 8 of 8

How to merge the columns into one by their rows?

  1. #1
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

    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. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

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

    Try this Macro

    Please Login or Register  to view this content.
    My Rules if you want my help:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

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

    Many thanks for the solution! It does work.

    Best,
    Gabriele

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

    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. #7
    Registered User
    Join Date
    11-11-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    46

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

    Quote Originally Posted by MarvinP View Post
    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. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Merge Columns to Rows
    By ilsley_excel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-28-2016, 02:09 PM
  2. Conditionally merge rows to columns
    By Pasch in forum Excel General
    Replies: 2
    Last Post: 09-12-2014, 10:02 AM
  3. Merge Duplicate Rows unique values into single rows for an infinite amount of columns/rows
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:43 PM
  4. [SOLVED] Merge multiple duplicate rows into columns - more than 65536 rows
    By a3des in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2013, 09:18 AM
  5. [SOLVED] Merge duplicate rows and merge the columns (approx 10 columns)
    By adfeddon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2012, 11:19 AM
  6. Merge duplicate rows and their columns
    By wali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2011, 06:35 AM
  7. Can I merge rows to columns
    By miche in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-15-2006, 05:50 PM

Tags for this Thread

Bookmarks

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