+ Reply to Thread
Results 1 to 13 of 13

Rows to columns with linebreaks

  1. 05-26-2014, 02:54 PM #1
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Rows to columns with linebreaks

    This is a little complex, so I'm not sure how it'd be done, but here goes:

    I have a list of election results for the British election in 2010 which I'm trying to compile into a format like this:

    \1

    The list of base data I have however has each constituency in columns rather than in rows:

    \1

    I know there's the copy and paste using the Transpose tool, but I don't really want to have to do this 650 times for each individual constituency: is there any way I can select all of it and do some kind of trick so that it knows when to go to a new row based on when the name of the constituency changes?

    Thank you!
    Reply With QuoteRegister To Reply
  2. 05-26-2014, 02:56 PM #2
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Rows to columns with linebreaks

    Hello and welcome to the forum , can you upload a sample book with atleast 10-20 rows worth of data to work with
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh
    Reply With QuoteRegister To Reply
  3. 05-26-2014, 03:01 PM #3
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Re: Rows to columns with linebreaks

    samplebook.xlsx

    There you are
    Reply With QuoteRegister To Reply
  4. 05-26-2014, 03:29 PM #4
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    Welcome to the Excel Forum.

    1. What version of Excel and Windows are you using?

    2. Are you using a PC or a Mac?

    3. Can we have another workbook with the same raw data in Sheet1?

    4. And, can we have another worksheet (manually completed by you) with the results you are looking for?
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  5. 05-26-2014, 03:33 PM #5
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Re: Rows to columns with linebreaks

    1. Excel 2010 on Windows 8
    2. PC

    I've attached the workbook, with the desired results in Sheet 2.

    samplebook.xlsx
    Reply With QuoteRegister To Reply
  6. 05-26-2014, 04:23 PM #6
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    Thanks for the new workbook.

    Detach/open workbook ReorgData barray garray - loremipsum - EF1013766 - SDG15.xlsm and run the ReorgData macro.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-26-2014, 04:29 PM #7
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    Detach/open workbook ReorgData barray oarray - loremipsum - EF1013766 - SDG15.xlsm and run the ReorgData macro.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-26-2014, 04:43 PM #8
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Re: Rows to columns with linebreaks

    How would I extend the macro so that it deals with more data?
    Reply With QuoteRegister To Reply
  9. 05-26-2014, 04:54 PM #9
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    How would I extend the macro so that it deals with more data?
    The above two macros were written for groups of six rows.

    1. More data in EACH group of rows?

    2. Or, more data in the number of columns?

    3. Or, could each group have a varying number of rows?

    I would have to see another workbook, with before, and, after worksheets.
    Last edited by stanleydgromjr; 05-26-2014 at 04:58 PM.
    Reply With QuoteRegister To Reply
  10. 05-26-2014, 04:58 PM #10
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Re: Rows to columns with linebreaks

    More groups of five. So instead of going down to Kettering, going even further.

    I've attached the full workbook, but it's quite a lot of data. There are 650 groups of data in total.

    samplebook.xlsx
    Reply With QuoteRegister To Reply
  11. 05-26-2014, 05:24 PM #11
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    Thanks for the NEW workbook.

    More groups of six.

    Detach/open workbook ReorgDataV2 barray colBCD oarray colBCDEFGH - loremipsum - EF1013766 - SDG15.xlsm and run the ReorgDataV2 macro.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 05-26-2014, 05:32 PM #12
    loremipsum is offline
    Registered User
    Join Date
    05-26-2014
    Posts
    6

    Re: Rows to columns with linebreaks

    Absolutely smashing, thank you so much!
    Reply With QuoteRegister To Reply
  13. 05-26-2014, 06:37 PM #13
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Rows to columns with linebreaks

    loremipsum,

    Thanks for the feedback.

    You are very welcome. Glad I could help.

    And, come back anytime.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Generate a text from 3 columns - with linebreaks
    By Wiid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2012, 12:48 PM
  2. Hyperlink Function: Mailto with Body containing linebreaks
    By Elipapa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2012, 04:36 PM
  3. [SOLVED] Removing linebreaks/whitespace? (Cleaning up my output)
    By CFJensen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-08-2012, 06:21 PM
  4. list values from multiple cells in one cell separated by linebreaks
    By kbka in forum Excel General
    Replies: 8
    Last Post: 06-06-2012, 11:09 AM
  5. How make linebreaks without turning on Word-wrap?
    By white-k in forum Excel General
    Replies: 5
    Last Post: 01-03-2008, 04:01 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1