Help with a macro formula to convert horizontal data into veritical data

**shapenote1** · 04-26-2013, 12:24 PM

I have spreadsheet which has a cost center listed going down vertical and for each cost center this is potentially 35+ project numbers which has budget info that run horizontally on the spreadsheet.

Is there a formual or a macro I can use so that I can move the horizontal column headings to line up veritcally with their respective cost center?

Any help or suggestions would be greatly appreciated!

Attached (if I uploaded this correctly) shows the current lay out and the desired lay out I need.

Book1.xlsx

**arlu1201** · 04-26-2013, 12:54 PM

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

**shapenote1** · 04-26-2013, 01:34 PM

This is only capturing the 2nd cost center (i.e. ABC56789). It is giving me the 3 columns of data I need, but it is not picking up the first cost center.

Also -- will this work if say I have 40+ cost centers going vertical and 40+ project numbers running horizontal?

For this example, I only pulled 2 cc and 7 project nubmers? How can the code be modified to caputure a larger number of each?

**arlu1201** · 04-26-2013, 01:46 PM

Did you run the macro on the sample file which you uploaded?

I ran it on the same file and it worked fine. The header is in row 2 and data starts in row 3.

The macro will take care of as many projects and cost centers are there. You do not need to change anything.

**shapenote1** · 04-26-2013, 03:24 PM

OK ... so I re-ran in on the file I posted. I had to include a blank row so that the title cost center appears in A2. It worked fine.

Then I ran it on an actual file I have where there are 163 cost centers listed vertically and 43 project numbers horizontally and it worked -- only up to a point. It cut off the last project on every cost center and also left of 30 cost centers all together.

Thoughts?