asked on

Taking a report and turning it into a pivot using VBA?

The title says it all. The attached file shows the original report and the desired output (Pivot Table). I have no idea how to get this to work!

I was told VBA could be the answer. I'm using Excel 2007 on Windows but i made the example file on a mac.

Good luck to anyone who tries!
Report-Example.xlsx

redmondb

Hi, sanjangeorge.

Please see attached proof of concept (all changes highlighted in green). The process I used...
(1) Select blanks in c3:c18 and add formula =IF($B3=$B2,C2,"")
(2) Select blanks in b4:b18 and add formula =B3
(3) Select a3:a18 and add formula =IF(COUNTIF(B3:F3,"")<>0,"",IF(SUM(COUNTIF(B3:F3,B2:F2))=5,"",B3))
(4) Turn on AutoFilter for a2:i18
(5) Filter on non-blanks in "Report Name". Copy filtered data (i.e. the header plus the 7 rows with data in Column A).
(6) PasteSpecial Values to the Output sheet.
(7) Create Pivot based on Output sheet.

There are loads of implicit assumptions in all this, so please try it against your real data and let me how you get on.

Thanks,
Brian.
Report-Example-V2.xlsx

sanjangeorge

ASKER

Hi Redmondb,

This certainly works on my data set but i need something more automated because of the volume of reports i need to process.

Sanjan

nutsch

Is the Month 1 Sales header always saying Month 1 Sales, or is it January Sales or February Sales or something else altogether? You say there can be many columns of description, but how do you tell you've got the first month of sales?

redmondb

Sanjan,

Thanks. As I mentioned, that was just a proof of concept - to confirm that my assumptions were correct.

Regards,
Brian.

sanjangeorge

ASKER

The title is variable, however it is always the first entry in that row past the report name.

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

Thanks, Sanjan.