Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Converting data from Long to wide

  1. #1
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365

    Unhappy Converting data from Long to wide



    Register for a FREE account, and/
    or Log in to avoid these ads!

    [COLOR=var(--primary-text)]Hi
    how I can reshape the data
    I want to put the values of NTL when DHSCLUS is 1 under DHSCLUS_1 and when DHSCLUS is 2 value of NTL under DHSCLUS_2. and so on

    I have tried using the formula but Excel is taking a lot of time.

    how i can do it with VBA, please


    [/COLOR]





  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    I can do this but really need a sample workbook with realistic data from you.
    Click image for larger version. 

Name:	2020-09-21_113118.png 
Views:	8 
Size:	12.3 KB 
ID:	9997
    I also need confirmation that that is what you want to happen but if it's not then supply a manually prepared 'expected results' sheet.
    Last edited by p45cal; 2020-09-21 at 11:37 AM.

  3. #3
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    I can do this but really need a sample workbook with realistic data from you.
    Click image for larger version. 

Name:	2020-09-21_113118.png 
Views:	8 
Size:	12.3 KB 
ID:	9997
    I also need confirmation that that is what you want to happen but if it's not then supply a manually prepared 'expected results' sheet.

    Yes this is wat I want , I have tried to do this with formula but my data is too big and the excel is taking lot of time.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    Again,
    Quote Originally Posted by p45cal View Post
    I can do this but really need a sample workbook with realistic data from you.

  5. #5
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Again,
    Please
    this is the link to the file
    https://clarkuedu-my.sharepoint.com/...Ea5Zg?e=9evUdu

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    In the linked-to file:
    https://app.box.com/s/vi7dy4wtz0laf8ttzr7izjwxj8ig56ec
    on the Raster_Pixel_Points_SpatialJ1_T sheet I've deleted all but the first 2 columns since all the rest were empty.
    In their place I've added a Power Query which looks like it gives you what you want, however, there are no DHSCLUST columns for numbers 18, 19, 20, 21, 108, 191, 221, 345, 385, 386, 449, 454, 496, 506, 508, 509, 510, 513, 535 & 573 because those numbers don't exist in column B (same applies to your pivot table on Sheet3).
    If you delete some data in that Power Query table, then right-click and choose Refresh, it takes about 2 secs to update, hopefully that's fast enough.

  7. #7
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    In the linked-to file:
    https://app.box.com/s/vi7dy4wtz0laf8ttzr7izjwxj8ig56ec
    on the Raster_Pixel_Points_SpatialJ1_T sheet I've deleted all but the first 2 columns since all the rest were empty.
    In their place I've added a Power Query which looks like it gives you what you want, however, there are no DHSCLUST columns for numbers 18, 19, 20, 21, 108, 191, 221, 345, 385, 386, 449, 454, 496, 506, 508, 509, 510, 513, 535 & 573 because those numbers don't exist in column B (same applies to your pivot table on Sheet3).
    If you delete some data in that Power Query table, then right-click and choose Refresh, it takes about 2 secs to update, hopefully that's fast enough.

    Thanks

  8. #8
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by ros3243 View Post
    Thanks
    Sir Can you please let me know who you did this.

    Thanks

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    Power Query:
    Click image for larger version. 

Name:	2020-09-23_143620.jpg 
Views:	7 
Size:	100.4 KB 
ID:	10014
    click the image to get a bigger view.
    Last edited by p45cal; 2020-09-23 at 11:33 PM.

  10. #10
    Seeker ros3243's Avatar
    Join Date
    Aug 2012
    Posts
    11
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Power Query:
    Attachment 10012
    click the image to get a bigger view.
    getting this message when I click on the link


    Invalid Attachment specified. If you followed a valid link, please notify the administrator




Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •