+ Reply to Thread
Results 1 to 21 of 21

Rearranging data from one sheet to another

  1. #1
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Rearranging data from one sheet to another

    Hello,

    I'm not sure if this is possible. Any assistance would be appreciated.

    I have about 20 columns headed things like Product Number, Size, Colour, Weight etc

    So for example, one record might be something like:

    Product number Size Colour Weight Col5 Col6 Col7 Col8 etc
    32456 Large Blue 10kg etc etc etc etc etc


    I have about 10,000 of these records.

    What I need is to rearrange them in another sheet which just has 3 columns.

    Product number
    Attributes
    Values


    A record would look something like
    Product number Attributes Values
    32456 Size Large
    Colour Blue
    Weight 10kg
    etc etc
    etc etc
    etc etc

    Is it possible to transform the first format in the first spread sheet to the second format in the second sheet?

    Thanks
    Last edited by AdamBee; 10-31-2012 at 05:45 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Rearranging data from one sheet to another

    Use Pivot Table.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Thanks.

    I tried but I keep getting an error message saying the PivotTable field name is not valid.

    Presumably because some of the cells are blank so it looks like a PivotTable won't work.
    Last edited by AdamBee; 10-31-2012 at 06:27 AM.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Rearranging data from one sheet to another

    Attach your sample file.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    sample.xlsx

    Attached.

    As you can see, sometimes, cells will be blank.

    Thanks.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Rearranging data from one sheet to another

    I tried a pivot table but it does not work for your type of data.

    Which are the columns to be used for the re-arrangement?

  7. #7
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    All of them.


  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Rearranging data from one sheet to another

    So it should be like this -
    Product Code Attributes Values
    12345 Material xx
    Primary Color xx
    Secondary Color xx
    Brand xx
    Size/Type xx
    Dimensions xx
    etc
    etc
    etc
    etc
    etc
    etc

  9. #9
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Yes, that would be one record.

    Then underneath that would be the next record and so on for all 10,000.

    Thanks for the help.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rearranging data from one sheet to another

    try
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Quote Originally Posted by jindon View Post
    try
    Please Login or Register  to view this content.

    Thanks for your help but it gives the error message

    'Run Time Error '9':

    Subscript out of range

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rearranging data from one sheet to another

    Which line?

    Working here.
    Attached Files Attached Files
    Last edited by jindon; 10-31-2012 at 08:30 AM.

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Rearranging data from one sheet to another

    The subscript range is linked to the sheet name. Check that the sheet name you have in your workbook matches jindon's code with respect to spelling and case.

  14. #14
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Yes, I sorted out that subscript error.

    jindon, it kind of works!

    That file I uploaded was just a small sample. When I apply your code to the proper big spread sheet it misses out the first column, then lists the next 6 columns only and ignores all the others after that.

    Any ideas?

    Thanks.

    *edit* in the file you uploaded it also misses out the first column 'material'.
    Last edited by AdamBee; 10-31-2012 at 08:46 AM.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rearranging data from one sheet to another

    The code was written specially for the data(sheet layouts) that you have uploaded.

    I can not say anything without seeing your actual sheet layouts.

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Rearranging data from one sheet to another

    Jindon is right. Thats why, AdamBee, you should ensure that your sample file matches exactly with the original file with respect to layout. The data can be dummy.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rearranging data from one sheet to another

    Quote Originally Posted by AdamBee View Post

    *edit* in the file you uploaded it also misses out the first column 'material'.
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    It's the same format. Except, aside from the product number there are actually 70 other columns in the main spreadsheet, not the 12 in the sample.

    I appreciate the help.

    Cheers

  19. #19
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Hello again,

    I have tried to adapt the original code jindon wrote to work with the full spreadsheet but I can't do it.

    I have attached the format the full spreadsheet is in. It is exactly the same format as the sample I uploaded before with exactly the same layout and the exact same requirements except there are 74 extra columns and not 12.

    sample2.xlsx

    Is there any way the existing code you wrote jindon can be easily adapted for 74 instead of 12? I have tried but to no avail.

    Regards

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Rearranging data from one sheet to another

    Quote Originally Posted by AdamBee View Post
    Is there any way the existing code you wrote jindon can be easily adapted for 74 instead of 12? I have tried but to no avail.

    Regards
    Working with 72 Extra columns.
    No change of code.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    10-31-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Rearranging data from one sheet to another

    Quote Originally Posted by jindon View Post
    Working with 72 Extra columns.
    No change of code.
    Thanks again for your help - much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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