Unpivot and stack data

Hi yching,

After many questions similar to this, where people wanted to convert Crosstab style table to better formatted Excel tables, I wrote some generalized code. It will convert Crosstab tables but you present a newer problem. The merged cells above the table showing year present a real problem. If you can remove those and make them like my sheet2 I can do the rest. See the attached.

Hi,

The way my macro works is this. I assume row 1 has the headers that go with the columns that need repeating. In the normal Crosstab table there are names down the rows and dates across the top in row 1. Sometimes there are two or three columns that are needed to repeat. The reason the macro stops and asks is to allow the user to tell how many columns are "fixed" and need repeating.

By reformatting your data and having the first column of Name and the second of Year it then fit the format my macro could deal with. I haven't seen many tables like yours where you have two levels of column headings. Maybe if this was a standard, I'd add a second input box to my macro and deal with your structure also.

In most Excel problems the work gets a lot harder if merged cells are involved. By repeating your 2010 and 2011 like you suggest above might help but still doesn't fit the format that my macro expects to deal with.

I hope this explains more of what my macro does and why I need it like Sheet2. If not, keep asking. If you get lots of data in the exact format you suggested in post 1 then I might try to improve my code to fit your problem. Let me know.

Give me an example of what your real data looks like and I'll scratch my head a bit to see if my code can deal with your problem.

You can attach a sample file by clicking on "Go Advanced" and then the Paper Clip Icon above the advanced message area.

OK yching here you go,

I've added two subs to do your sheets. The first merges the column headers together into a single cell. The second fills in the blanks in column A.
To test it copy your Sheet1 data (all cells) to a new sheet. Then run the macro called "DoItAll". You need to tell the macro there are 3 fixed columns in your data.

If you want to put a new column in the converted table of Year, I'd suggest using a formula of =Right(D2,4) and fill it down.

I'm off to bed now in Seattle time. I hope this works for you. Remember to click the "Add Reputation" star below this post for all the work I may have saved you.