Converting data from Long to wide

R

ros3243

New member
Joined
Aug 22, 2012
Messages
11
Reaction score
0
Points
0
Excel Version(s)
365
[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
119964052_10218613998440960_714550652248195203_o.jpg


[/COLOR]



 
I can do this but really need a sample workbook with realistic data from you.
2020-09-21_113118.png
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:
p45cal said:
I can do this but really need a sample workbook with realistic data from you.
View attachment 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.
Click to expand...


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.
 
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.
 
p45cal said:
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.
Click to expand...


Thanks
 
Power Query:
2020-09-23_143620.jpg
click the image to get a bigger view.
 
Last edited:
You must log in or register to reply here.
Back
Top