Merge rows and Transpose data

[ravikiran]

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.



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

Regards,
Ravi.

 

[vletm]

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? ))

 

[bluesky63]

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

 

[ravikiran]

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.

 

[Marc L]

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 ?) …​

​

 

[bluesky63]

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.

 

[ravikiran]

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.

 

[Marc L]

So read again post #5 but well this time ! As this is very not a mind readers forum …​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[vletm]

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.