+ Reply to Thread
Results 1 to 9 of 9

Converting Rows Into Multiple Rows - Large Data Set

  1. #1
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Question Converting Rows Into Multiple Rows - Large Data Set

    With some assistance I managed to put together the necessary formulas to translate a dataset that needed each row splitting into multiple rows.
    The original thread can be found here;

    http://www.excelforum.com/excel-form...s-failing.html

    This approach is fairly resource intensive and my Laptop fails to generate results after a certain point and with the full data set it just fails, so I was advised to look at VBA, however its an area that I'm pretty weak on unfortunately.

    Can anybody point me in the right direction ?

    ROW TO MULTI ROW.xlsx

    Thanks,

    Nic

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting Rows Into Multiple Rows - Large Data Set

    It is not clear which columns you want to transpose. I followed your split sheet output.
    There is a button on sheet data and the out put is on new sheet created by the code itself.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Converting Rows Into Multiple Rows - Large Data Set

    Excellent AB33, this is the basic result I'm looking for.

    I'd also like to be able to add all the other fields, and the only complexity is the contact name and title.

    I've attached an example of the headings in the original data source (TAB: Original Format) and what I'm trying to achieve in the output (TAB: New Format)

    Basically all fields in blue and green need to be repeated for each set of Contact and Title in orange.

    Below is a simple example, the attached file shows the actual fields;

    Account Other Contact1 Title1 Contact2 Title2
    ACCNA STU Fred Boss Joe Director

    Account Other Contact Title
    ACCNA STU Fred Boss
    ACCNA STU Joe Director

    DATA TRANSLATION EXAMPLE.xlsx

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting Rows Into Multiple Rows - Large Data Set

    nisiwi,
    The output sheet is blank. Could you please add raw data in input sheet and few lines showing your desired result?

  5. #5
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Converting Rows Into Multiple Rows - Large Data Set

    Here's an update to that file AB33.
    Attached Files Attached Files
    Last edited by nisiwi; 02-20-2014 at 05:26 PM.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting Rows Into Multiple Rows - Large Data Set

    nisiwi,,
    There is button on sheet reform column S. The output is on sheet reform.
    The code is very sensitive to the layout of the data. If the lay out has changed, I need to change the code too.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Converting Rows Into Multiple Rows - Large Data Set

    Excellent AB33 - I will have to send you a beer (a few actually) donation!

    Actually, my fault I did change the layout to make it simpler, however it looks like the attachment wasn't' updated correctly.

    I've attached an update and will attempt to get my head around your code.
    Attached Files Attached Files
    Last edited by nisiwi; 02-21-2014 at 08:38 AM.

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Converting Rows Into Multiple Rows - Large Data Set

    Amended code.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-17-2008
    Location
    Kidderminster
    Posts
    18

    Re: Converting Rows Into Multiple Rows - Large Data Set

    Great, thanks AB33. Actually I had a go myself using your code as a starting point and come up with the same result :-)

    How would I ensure that columns K & L retain the prefix zero's ?

    How can I send you a small donation as thanks ?

+ 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 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
  2. Need help combining multiple rows and columns of data on a large document
    By kernel4485 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-22-2012, 12:53 PM
  3. Loop macro for multiple separate graphs from rows of a large data set
    By GlennToms in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2012, 02:12 PM
  4. Replies: 1
    Last Post: 05-23-2012, 05:49 AM
  5. [SOLVED] Converting rows to columns on a large scale
    By Bootsy in forum Excel General
    Replies: 0
    Last Post: 02-23-2005, 01:06 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