Macro for data transformation

[narikisas]

Hi,

I have a data in excel which need to be transformed/structured so that it can be pivoted to get desired results. The first four columns of the file will give product information and the next columns gives the product ID, store name and price information. For each of the store name associated to the product, a different column is created along with their price value. The first four columns should get repeated the number of times the store names are there (in the columns) in the data. The data should get appended top to bottom with their specific store name, product ID and price.

Attaching sample data and desired view.

Any help here will be appreciated.

Thanks,
Sasikiran

[Olly]

Is your profile up to date - are you still using Excel 2007?

[narikisas]

My bad! I just updated my profile. I am using the latest version of excel.

[Olly]

You could use Power Query (Get & Transform Data):

Please Login or Register  to view this content.

[narikisas]

Thanks a ton! It really helped - Resolved

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.