Excel trick to convert a downloaded report into a flat file

This posting will be more technical than usual as it will be showing a lot of formulas. Right now I’m helping my boss prepare the budget file in preparation for budgeting season. One of the things we want to do is improve a particular section of the file which was giving her headaches: I’ll call it the Occupancy calculations.

The data she gets comes in a nice pretty format I’ll call the report form but you have to manipulate it to do some calculations. Apparently it was a lot of work to manipulate the data and when new sites come on line or old sites go away, it was painful to change the file and all of the formulas. We’re thinking of figuring out a way to just plop in the report and let Excel do the work.

My first thought was “Pivot tables!” but the report is not downloaded in a flat file format. It has GL accounts and sites down the left side and monthly columns across the top and the values sitting in the body of the table, at the intersection of GL/site and month. The months running across the top is what is preventing us from doing a pivot table. The months need to run along the left side so that each value will have a GL/site/month data attached to it.

To restate it in another way, pretend you have 30 lines of rows in your report for GL/site combination and 12 columns running across the top. Now you want 30 times 12 rows, each row containing GL/site/month/value.

How do you convert such a table into a flat file?

With a lot of formulas!!!

First off, I went ahead and “counted” the rows in the report using 1, A1+1, A2+1, A3+1 and so on down the left column A. I also asked if B2 was empty, then make A empty too. So the full formula is : If(B2=””,””,A1+1).

Second thing I did was to count how many rows of data there were in the report with COUNTA(B2:B1048576).

Then, in another tab for the flat file, I proceeded to number the left hand column A again but this time just using the formula A1+1, A2+1 and so on down. The end result of your flat file should contain 12 times COUNTA rows.

Continuing with the flat file tab, we will proceed with bringing in the GL/site information (and whatever else you need). Continuing with the 30 rows in your report theme, the first 30 rows under column A in the flat file maps directly with the 30 rows in the report tab. You could simply point to the B1, B2, B3 …cells (assuming data starts in row 1) and bring in the GL or Site or whatever. These would be for the January numbers. The next 30 rows in the flat file (rows 31 to 60) would be the February numbers. I can’t do any VLOOKUPs (if I were choosing to go that route) because the report has only 1 to 30 rows, not 31 to 60. But if I do (A1 – cell containing COUNTA), I will get back to numbers 1 through 30, which can be used for VLOOKUPs. But… the March numbers would be on rows 61 through 90. In order to get back to the 1 through 30, I have to figure out which “cycle” I’m on and multiply that (cycle number-1) to the count before I can do (A1 – count). For the March, that would be the 3rd cycle so I would simply do (3-1)*30 to get 60 and then subtract 60 from the A numbers (61-60, 62-60 and so on) to get the numbers 1 through 30 for VLOOKUPs. The formula to handle the cycle issue becomes: A1 – INT(A1/cell with COUNTA)*(cell with COUNTA) where A1 can be substituted with A1, A2, A3 and so on down the column in the flat file tab.

However, there is one little problem. In a 30 row report, at the inflection point (my term) of 30, 60, 90…the prior formula becomes zero, so I make the final tweak to the formula to make it be the COUNTA cell.

Or something like: If(A1 – INT(A1/cell with COUNTA)*(cell with COUNTA)=0,(cell with COUNTA),A1 – INT(A1/cell with COUNTA)*(cell with COUNTA)).

This formula gets fed into the VLOOKUP formula.

Whew! All of that for bringing in the GL and site information.

I think I will stop here (it’s getting late) and pick back up again on Friday or Saturday to talk about bringing in the month and value data into the flat file tab. Pulling in the months requires some hairy explanations which would make this post too long.

[divider]The Formulas[/divider]

Assuming your report has 20 rows of data with months going across the columns, the flat file tab will have 20 x 12 data points.

Assume the headers are on row 1 and data begins on row 2.

Column A in the flat file tab = numerical counting from 1 to whatever (at least up to 20 x 12 rows). Formula: If(B3=””,””,A2+1) starting in cell A3, with A2 being 1.

Column B will be the GL information and column C the site information. Formula will be like:

VLOOKUP(IF($A2-INT($A2/’Report’!$B$1)*’Report’!$B$1=0,’Report’!$B$1,$A2-INT($A2/’Report’!$B$1)*’Report’!$B$1),’Report’!$A$5:$C$20,2,FALSE) for GL.

VLOOKUP(IF($A2-INT($A2/’Report’!$B$1)*’Report’!$B$1=0,’Report’!$B$1,$A2-INT($A2/’Report’!$B$1)*’Report’!$B$1),’Report’!$A$5:$C$20,3,FALSE) for site.

‘Report’!$B$1 contains  COUNTA(B2:B1048576).