Use a macro to turn an ugly data export into a clean tabular file

[tml2424]

I have no idea if this is possible but when I do a data dump from an accounting system, I get a lot of data (80,000+ rows) with terrible formatting. I'd like to be able to pivot it and run analysis but it would take forever just to get it into a decent format. I've attached a file showing what the data looks like and what I want it to look like. Essentially, I want to be able to remove the headers, remove the totals columns and repeat the data so that the format is in tabular. Is this even possible?

Thanks in advance for looking.

[torachan]

Instead of exporting the report as a formatted Excel file does your accounts program allow you to export the same report as a .csv file (most packages such as Sage/QuickBooks etc. do)
If this is possible most of your problem is solved, then merely open the .csv file in Excel and all your columns will be neatly tabulated.
torachan.

[zzzeddy]

Hi

Of course it is possible. It's Excel.

I have added a routine to do the tidy-up, using your sample file.
I assigned the macro to a button (near column M) for convenience.

zeddy
Excel Cleaning Dept

[tml2424]

Thanks for the reply. Unfortunately, and surprisingly, our system can't export via csv.

[tml2424]

zzzeddy. This is a brilliant macro! Worked like a charm. Thank you!

[zzzeddy]

Hi

Thank you for letting us know. We like feedback.

If you wanted a faster deluxe sheet cleaning service, we could probably improve that vba code.

zeddy
Excel Cleaning Dept