EXCEL 2003 - Transpose to dynamic array for Data Import

[RobNauticus]

Hello all,

Client has a data page from external source in an excel format for weekly data. The weekly data is placed into columns for each week. There are 3 sections that get added to each week ( a new column is added for each section each week, see Gross, Cans, Traffic below).

I am designing a workbook that will translate this into a suitable format for importing into my access database for advanced reporting.

I need to transpose the data into rows with their corresponding labels intact for each row.

My problems are many, since the new columns are added each week, I cannot set a specific range to pull from. Furthermore, I am not sure how to get the data to create a new row for each corresponding entry on the original.


Current View of Data: (There is more data that is hidden!)

Desired Output:

I am able to do this manually, but it would be much better to have this automated so the client can do this themselves.
Any help would be GREATLY appreciated!!!

Thanks,
Rob

 

[SkipVought]

Hi,

Seems to me that you'll need to write some VBA to accomplish this.

Please report in VBA Visual Basic for Applications (Microsoft) Forum707.

Skip,

_[red][/red]