• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge rows and Transpose data

ravikiran

Member
Hi Excel Gurus,

Need help to create a macro to do the following transformation. I though using a Dictionary will help to do the job. But I am at loss on how to do it. Any help is appreciated.

64868 64870

I have a larger worksheet over 15K rows to be transformed.

Regards,
Ravi.
 
ravikiran
Your sample has some possible challenges ...
eg if there would be more T Code's after E ...
but ... without any code ... test this sample
(( where would come that one zero? ))
 

Attachments

  • ravikiran.xlsb
    10.1 KB · Views: 10
Hi Ravi,
Power Query is the way to go

you can append data in the blue source table, and right-click refresh and see result
 

Attachments

  • Transformed_Chris.xlsx
    17.9 KB · Views: 2
Thanks vletm and bluesky.

The pivot seems to do the job. I will try and apply it to the main data.

@bluesky63 - This seems to be a very good option especially for big data. I need to check if I can get power query working on my office machine.

I only looked at macro as there is more transformation required. I will rather try and use one of these options.

Thanks again.
 
Hi !​
I don't know if it can be achieved by macro (as macros disappear since 1997 !)​
but can be easily done under a VBA procedure just using an Excel Basics worksheet function​
or via a VBA Collection rather than any Dictionary, whatever, depending on how is your real worksheet (uniques ?)​
and on the expected result worksheet (just data copied, any calculation ?) …​
 
Hi
Actually power query can do more than that, so more I had transformed quite a number of tedious excel steps to PQ, including text analytics, pq M language is quite powerful and it is easier for people like me who are slow in VBA, DAX etc, as most of the steps can be done by UI just once, and subsequent just right click and refresh

If you can upload your original requirement with mock-up data ( no need many records), I can take a look on the possibility of fully automate it

Of course if you prefer VBA then other forum members can advise.
 
Hi All,

Took longer. Been in struggle with IT get a power pivot to be installed on my machine. Didn't get the 2016 version yet.

Any chances this can be automated with VBA? As there are other tasks I need to execute once the data is transposed.

Thanks to all.
 
So read again post #5 but well this time ! As this is very not a mind readers forum …​
 
ravikiran
So far Your needs can do as #2 reply - manually, with some clicks.
If You cannot explain 'what do You really need?' then You'll have a challenge.
 
Back
Top