+ Reply to Thread
Results 1 to 9 of 9

Converting Rows to Columns

  1. #1
    Registered User
    Join Date
    09-07-2016
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    4

    Converting Rows to Columns

    I have a list of data that is in rows and I need to copy it into columns at each change in the location. The number of people in each location will vary from location to location but everyone from one location needs to be on one row.

    Example.

    ID Name Location
    125 John 0986
    128 Mike 0986
    154 Bill 0967
    123 Steve 0967
    157 Adam 0967

    I need each location on one row.

    ID Name ID Name Id Name
    125 John 128 Mike
    154 Bill 123 Steve 157 Adam


    Does anyone have any suggestions? Need a solution as soon as possible. Searched online and the forum but haven't found any options. Sample file attached.
    Attached Files Attached Files
    Last edited by ahoneycutt; 09-07-2016 at 11:24 PM. Reason: Attached sample File

  2. #2
    Registered User
    Join Date
    03-08-2016
    Location
    Canada
    MS-Off Ver
    2015
    Posts
    69

    Re: Converting Rows to Columns

    A sample workbook of what you have and what you need would be ideal. Be happy to take a look.

  3. #3
    Registered User
    Join Date
    03-08-2016
    Location
    Canada
    MS-Off Ver
    2015
    Posts
    69

    Re: Converting Rows to Columns

    If you copy your data and press on a blank cell then right click you will see paste special then paste special again which a dialog box will appear from there pressing tranpose may give you the results you seek?

  4. #4
    Registered User
    Join Date
    09-07-2016
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    4

    Re: Converting Rows to Columns

    I have tried attaching a file but the attachment option is not available and it is not allowing me to upload an image either. Not sure what is going on.

    Literally the example I posted is what the data source looks like.

    Rows of people in one location with multiple locations listed. I need everyone at the same location on one row.

    I'll keep trying to get it to upload.Capture.PNG
    Last edited by ahoneycutt; 09-07-2016 at 11:19 PM.

  5. #5
    Registered User
    Join Date
    09-07-2016
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    4

    Re: Converting Rows to Columns

    Thanks but transpose does not work. I have over 4800 rows. I need them separated by location.

  6. #6
    Registered User
    Join Date
    09-07-2016
    Location
    Charlotte, NC
    MS-Off Ver
    2016
    Posts
    4

    Re: Converting Rows to Columns

    I was finally able to get the attachment to save.
    Attached Files Attached Files

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,801

    Re: Converting Rows to Columns

    Hi ahoneycutt and welcome to the forum.

    I have assumed that in the example output row 7 the duplicates of 384484 Cheryl Ray are typos.

    In the attached the output is below yours.

    In the Location column I copied / pasted the source and applied 'Remove duplicates'. It seemed simpler.

    Array-enter this formula in G12. Then fill down and across to L17.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Attached Files Attached Files
    Dave

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,801

    Re: Converting Rows to Columns

    An afterthought.

    Since there are only two columns to consider in the source range you can replace that array formula with this one. It does not have to be array-entered.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I should have thought of that earlier.

  9. #9
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    511

    Re: Converting Rows to Columns

    I propose the following formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Converting rows to columns and adding extra rows as required
    By teco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-09-2016, 04:18 PM
  2. [SOLVED] converting rows to columns
    By sreejuster in forum Excel General
    Replies: 6
    Last Post: 10-23-2014, 02:10 PM
  3. Converting multiple columns to rows / Converting to Panel Data
    By Stuart11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2013, 05:50 AM
  4. Converting columns into rows
    By Max Wallace in forum Excel General
    Replies: 5
    Last Post: 03-23-2013, 07:32 PM
  5. Converting Rows to Columns
    By mschlenker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2011, 12:48 PM
  6. Converting columns to rows for multiple rows
    By khscruz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2011, 11:59 AM
  7. (Again) Converting rows to columns
    By nickr1954 in forum Excel General
    Replies: 4
    Last Post: 01-28-2006, 09:25 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