+ Reply to Thread
Results 1 to 8 of 8

Turn large Horizontal list into Vertical list?

  1. #1
    Registered User
    Join Date
    03-13-2020
    Location
    Ohio
    MS-Off Ver
    Office 365
    Posts
    11

    Turn large Horizontal list into Vertical list?

    Good Morning Everyone,

    Is there a way to take a huge one Column list and convert it into a vertical list? I have a one column list with over a 1500 rows and I need to take every eleven rows and flip them horizontally. I've attached a smaller example of this (hopefully).

    Have a nice weekend

    Rob
    Attached Files Attached Files

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

    Re: Turn large Horizontal list into Vertical list?

    Hi osurob,

    Here is a formula that will do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See it in the attached file, the yellow cell. Pull it across and down.

    Vert to Horz using Indirect Columns and Rows.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: Turn large Horizontal list into Vertical list?

    Formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Turn large Horizontal list into Vertical list?

    01 = 11

    c1 =(INDIRECT(ADRESS((COLUMN()-2)+(ROW()-1)*$O$1,1))) and drag across.
    Last edited by oeldere; 04-18-2020 at 11:35 AM.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

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

    Re: Turn large Horizontal list into Vertical list?

    Hi czeslaw,

    I needed to lookup the "=@Index()" as I've never seen it before. I find a good article at:
    https://support.microsoft.com/en-us/...rs=en-us&ad=us

    Thanks for showing me something else I've never known in excel...

  6. #6
    Registered User
    Join Date
    03-13-2020
    Location
    Ohio
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Turn large Horizontal list into Vertical list?

    I can't thank you enough MarvinP. Your answer just took my list down from 8500+ rows to now just 751 rows. Have a great weekend Marvin and thank you again.

  7. #7
    Registered User
    Join Date
    03-13-2020
    Location
    Ohio
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Turn large Horizontal list into Vertical list?

    Also thank you Czeslaw and oeldere you guys are amazing!

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

    Re: Turn large Horizontal list into Vertical list?

    Here are 3 different ways to Transpose your data. Number 3 is very creative and I wanted to try it on your data but needed to re-find it to remember how.

    https://www.youtube.com/watch?v=yYVokk0NdiI

+ 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. Horizontal list to vertical list (not transpose)
    By Julie29 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2020, 06:50 AM
  2. Replies: 1
    Last Post: 03-23-2020, 10:45 AM
  3. Create vertical list from non uniform horizontal list
    By simonwait in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-31-2019, 08:36 PM
  4. [SOLVED] turn vertical list to horizontal list start new row when change in column A, B and E
    By drosew in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-13-2016, 01:13 PM
  5. Switching a Horizontal List to a Vertical List
    By mrvp in forum Excel General
    Replies: 2
    Last Post: 02-28-2012, 07:22 PM
  6. Converting vertical list into horizontal list
    By Frenchtom in forum Excel General
    Replies: 2
    Last Post: 09-12-2011, 03:55 PM
  7. [SOLVED] Converting a large vertical mailing list into a horizontal format
    By Kevin VanHalen in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-30-2005, 08:07 AM

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