[SOLVED] Insert Rows and Transpose Data

Hi Guys,

I am new to the forum and to the world of excel Macros programming and would appreciate if some one can help me on a particular problem that I have. I have a excel sheet which has multiple rows as each row corresponds to a unique ID. Each unique ID has 5 values associated with it and are displayed on corresponding columns. I need to do the following. For each unique ID I need to insert two rows after it and transpose the 2nd value below 1st and 4th and 5th value below 3rd value in the sheet. I have also attached a sheet for what I need help on so that it is easier to understand the problem. Although I have only represented 3 rows for simplicity, but I have to to this for a worksheet which has more than 5000 Unique ID's. So it would be awsome if the macro can recognize the next unique ID and keep repeating this task

I appreciate the effort and time spent to do this

Many thanks
sks262

Hi sks262 and welcome to the forum,

Find the attached where I've turned you Crosstab table into a normal Excel Table and did a Pivot Table on it. I have also included the code to automate this task. Let me know if the Pivot Table answer will work for you.

Hello sks262,

Welcome to the Forum!

In addition to Marvin's solution, this one use VBA. It will add the new lines, transposed data, and formatting to all the cells. The macro starts on row 4 as does your example. If you actual data starts on a different row, you will need to make this change to the macro code. It is bold below. A button has been added to run the macro in the attached workbook.

Please Login or Register  to view this content.