Converting a table to a Pivotable?

You will need a VBA routine to convert the "strung out" data into a normalized format. This is a commonly asked question, so I think I'm going to write some generic code to do it.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I'll lodge my normal complaint and then get on with it. This should really be a database application. But your company is afraid of databases and they never did it that way before, so we'll have to pave the cow path and put the cows on rollerblades. This looks like it is based on an old paper ledger book design. Nothing could be worse for automation. Having said that, yes, we can write code to automate it.

The first thing that has to be fixed is that you want a monthly report, but you track data by week and on a separate worksheet for each week. So all of this data will have to be collected to one sheet. Then we can begin the task of normalizing it.

Actually, looking at the data layout, we probably only need to do the consolidating. I don't think normalization will be necessary. Technically speaking, even consolidation would not be necessary, you would just need formulas with 52 terms . I'm not going to do that! I'll get on this and get back to you.

There are a couple of additions to the workbook.

First is the Control Panel. This sets up things for you.

Cell G1 has the first of the year. Cell I1 is the first Monday of the year. This generates the table in columns F:I.

Select a week number in cell B2 and then click on Make Sheet. This button copies the template worksheet, gives it a name appropriate for the dates selected, and puts it in immediately after the Start sheet.

The templates have “hidden dates" in cells F4 and G4 (they are white text) – these are used for data validation. On each sheet, you should only be able to enter dates for the selected week

The template sheet may be hidden if you wish.

Select the weekly sheet and do the data entry. The code and formulas count whatever is in the cell. It does not have to be an “x.” Blank cells are not counted.

After filling in the data for the week, click on the consolidate button. This button consolidates the data onto the data sheet. The data sheet is used in the formulas on the YTD sheet. Each column contains monthly totals except for the current month which is month to date.

The data sheet may also be hidden.

The Start and End sheets are used to tell the program what sheets to consider for consolidation. Any sheet between these two sheets will be rolled up into the data.

These sheets may also be hidden if you wish. You can also hide past week data sheets.

On the YTD summary, I look back to the Control Panel sheet and get the last 12 months with the current month being the latest. This will roll over automatically each month. If you want, I can lock this back down to May 1, 2016 – it’s a matter of tinkering with the formulas on the control panel sheet.

The YTD cells have formulas in them. If you overwrite these formulas with data, the formulas will be lost, so do the data entry on the weekly sheets. The exception is the no shows. There are no formulas in that row other than the total.

Inertia is a hard thing to fight. In the long run, a database would be better, however, it turned out not to be as bad as I thought. The weekly sheets are consistent which makes the coding a lot easier.