Macro to Unpivot a Flat Data with Merged headings

Dear All,

I am using Excel 2010 and have problem as shown on Input Sheet on attached sample

I have data with Merged Cells in Row 1

Cells A1, B1 and C1 are Blanks

Data shown is for few rows. Actual Data is several rows down

Merged Cells starts from 1st Column in each Data Set. Last Column under each Region is not under the Merged Cells
Heading under each Region also will differ

The data is separated by a Blank Column in between

Details are Text Entries

Data are numeric entries

I want a macro to give me the result as shown on the Output Sheet

Any help would be appreciated
TIA
Rashid Khan

Try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.

Try

Please Login or Register  to view this content.