+ Reply to Thread
Results 1 to 6 of 6

Convert Vertical list to Horizontal

  1. #1
    Registered User
    Join Date
    08-13-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Convert Vertical list to Horizontal

    I have check other methods for doing this but none of the solutions seem to match what I want.
    My horizontal list is like this:
    A 1
    A 2
    A 3
    A 4
    B 1
    B 2
    B 3
    B 4
    C 3
    D 1
    D 2
    D 3

    And I want it to look like this:

    A B C D
    1 1 3 1
    2 2 2
    3 3 3
    4 4

    If anyone can help with some formulas or vba code, I'd be grateful.

    Thanx Paula.

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Convert Vertical list to Horizontal

    Here's a formulas tinker which will deliver it ...

    Assume your source data as posted is in A2:B13

    The working area ...
    Put in D1: =IF(COUNTIF(A$2:A2,A2)>1,"",ROWS($1:1))
    Copy down to D13
    Put in E1:
    =IF(COLUMNS($A:A)>COUNT($D$2:$D$13),"",INDEX($A$2:$A$13,SMALL($D$2:$D$13,COLUMNS($A:A))))
    Copy across to H1
    Put in E2: =IF($A2=E$1,ROWS($1:1),"")
    Copy across to H2, fill down to H13

    Now for the desired Results area ..
    Put in J1: =E1, copy across to M1
    Put in J2:
    =IF(ROWS($1:1)>COUNT(E$2:E$13),"",INDEX($B$2:$B$13,SMALL(E$2:E$13,ROWS($1:1))))
    Copy across to M2, fill down to M13

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Convert Vertical list to Horizontal

    Hi

    One way could be this.

    With your data starting in A2 & B2, in D2 put this Array Formula( Control+Shift+Enter) and copy down.

    =IFERROR(INDEX($A$2:$A$100;MATCH(0;COUNTIF($D$1:D1;$A$2:$A$100);0));")

    Highlight range F1:I1 and type this also Array formula.

    =TRANSPOSE(D2:D5)

    In F2 and copy down and across, put this also Array formula.

    =IFERROR(INDEX($B$2:$B$1000;SMALL(IF($A$2:$A$1000=F$1;ROW($B$2:B$1000)-1);ROW(B1)));"")

    Change all the semi-colons in my formulas to comma.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Convert Vertical list to Horizontal

    Pl see attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-13-2012
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Convert Vertical list to Horizontal

    Thanks all for the help and quick responses. The INDEX formula worked exactly as I wanted. Paula.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Convert Vertical list to Horizontal

    If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

    New quick method:
    Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word "Title" you will see a dropdown with the words "No prefix".
    Change to "Solved"
    Click Save

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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