+ Reply to Thread
Results 1 to 11 of 11

Restructuring data from single row to multiple rows

  1. #1
    Registered User
    Join Date
    01-18-2016
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Restructuring data from single row to multiple rows

    Iíve got some data in the format attached where there is multiple columns of data, however I need it in a format where the columns are transposed into new rows, but retain some of the original row data.

    Is there a way to have a formula on another worksheet that would automatically add new rows with the data in this format? The complication is that I need a new row for each fruit. Ideally I would also have a way to assign a unique userID based on the user column.

    Any help would be greatly appreciated! Thanks in advance.

    excel example.xlsx

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    26,610

    Re: Restructuring data from single row to multiple rows

    Is there a maximum number of "Fruits"?

  3. #3
    Registered User
    Join Date
    01-18-2016
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Restructuring data from single row to multiple rows

    Hi John,
    Yes, there's about 40 'fruits' in total in my real data.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,043

    Re: Restructuring data from single row to multiple rows

    The columns labelled Apple Banana and Cherry columns C, E, G are irrelevant aren't they...since they will ALWAYS contain Apple Banana Cherry?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,043

    Re: Restructuring data from single row to multiple rows

    Here's a VBA solution with output on Sheet2, you'll need to modify this to reflect your true spreadsheet.

    Please Login or Register  to view this content.
    By the way your results are incorrect, Adam has only 1 cherry not 2.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    26,610

    Re: Restructuring data from single row to multiple rows

    Another VBA offering:

    Click RUN on Sheet2. I suspect it will need adjusting to your "real" workbook

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-18-2016
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Restructuring data from single row to multiple rows

    Thank you so much for your replies. I can see this working, and the data comes out exactly as I had asked, however I didn't anticipate it would require a script. I guess I was hoping it would be formula based so I could customise it to our data more easily. I'll perhaps add a more useful sample data so you can see exactly what we need to do.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    26,610

    Re: Restructuring data from single row to multiple rows

    Yes .... don't give up!. BUT ensure that the data is exactly as you will have it "real life" to avoid solutions being provided which do not meet you needs

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,849

    Re: Restructuring data from single row to multiple rows

    Created a helper column in Column I and used below formula
    I4=COUNT(C$4:H4)-COUNT(C4:H4)+(COUNT(C4:H4)>0)
    K4=IF(ROWS(K$4:K4)>MAX($I$4:$I$6),"",INDEX(A$4:A$6,MATCH(ROWS(K$4:K4),$I$4:$I$6)))

    L4=IF(ROWS(L$4:L4)>MAX($I$4:$I$6),"",INDEX(B$4:B$6,MATCH(ROWS(L$4:L4),$I$4:$I$6)))

    M4=IF(K4="","",INDEX(INDEX($A$4:$H$6,MATCH(ROWS(L$4:L4),$I$4:$I$6),),SMALL(INDEX(ISERROR(1/INDEX($C$4:$H$6,MATCH(ROWS(L$4:L4),$I$4:$I$6),))*10^10+COLUMN($C$4:$H$6)-1,0),ROWS(M$4:M4)-LOOKUP(ROWS(M$4:M4),$I$4:$I$6)+1)))

    N4=IF(K4="","",INDEX(INDEX($A$4:$H$6,MATCH(ROWS(M$4:M4),$I$4:$I$6),),SMALL(INDEX(ISERROR(1/INDEX($C$4:$H$6,MATCH(ROWS(M$4:M4),$I$4:$I$6),))*10^10+COLUMN($C$4:$H$6),0),ROWS(N$4:N4)-LOOKUP(ROWS(N$4:N4),$I$4:$I$6)+1)))
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  10. #10
    Registered User
    Join Date
    01-18-2016
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Restructuring data from single row to multiple rows

    Thanks again for all your suggestions. The data is coming from a form, and the form has become even more complicated. I'm not sure what we want is even possible without manually editing the data.

    I've attached some sample data in the actual format it's coming out of the form. Essentially we need a new row for each wildlife observation, duplicating some column data, but with the SpeciesCategory and Species alongside quantities. I've put in an example structure in a new worksheet.

    If you are up for a challenge I would be overwhelmed with appreciation and admiration if you can solve this.

    example-form-data.xlsx

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    26,610

    Re: Restructuring data from single row to multiple rows

    Much more complex than your original posting!

    You have "Species" categories which have to be "determined" from the raw data so it is not a simple matter of transcribing the data.

+ 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. Replies: 9
    Last Post: 11-11-2015, 08:19 PM
  2. [SOLVED] Creating multiple rows from single row of data
    By RaydenUK in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 04:08 PM
  3. Replies: 14
    Last Post: 08-20-2014, 05:46 AM
  4. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  5. Replies: 5
    Last Post: 05-25-2013, 07:12 AM
  6. Consolidating multiple rows of data into single row
    By fredenbp4 in forum Excel General
    Replies: 3
    Last Post: 11-05-2009, 01:14 AM
  7. Replies: 1
    Last Post: 03-18-2009, 04:18 PM

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