Excel 2013 - Convert Format/Layout of spreadsheet quickly

[satanicguardian]

Hi guys, I'm not sure if I'm even asking the right question here..but here goes!

(Quick Background, I work in telecoms/b2b mobiles and am quoting a customer a mobile deal, one of the conditions is that I can provide them with a correctly formatted bill breakdown in excel format each month)

So presently the bill would be downloaded from an online portal looking similar to this (numbers are blanks and randoms, no personal information included)

The customer is used to seeing all their breakdowns in a particular format, part of the deal is that they can continue to see new bills in the same way.

So basically I want to find out if I can manufacture a quick way (perhaps a macro of some kind?) that I can quickly run on the freshly download spreadsheet and make it look somehting more like this:



If anyone has any idea how I'd go about doing this, I could really use some help!

Thanks!

 

[dazzerd]

You have 4 choices.

1: Assuming the source csv comes from a datawarehouse of some sort ask your BI team to produce the output in that format.

2: Do it manually each month... not great I know

3/4 (3)Write some vba to manipulate the raw format. You will need to know VBA and the Excel object model. It will need to be in .xlsm or .xlsb format. (4) Create a template of the format using formulas which feeds off the raw data.

You also need to consider other problems in relation to 3/4: how is your client going to get hold of the file in the new format (you cannot have vba or formats in a csv file), can you put such files (with embedded code) on your portal? Much to think about here but it all depends on your IT infrastructure.

 

[satanicguardian]

Hi Dazzerd, thanks for your reply - I think it would end up being the visual basic solution but it's all academic now because some smug sodding rich dealer went in and offered the customer an extra £3000 which I can't and won the deal.. b**tards!

Thanks anyway!