Turning columns into tables

**jomili** · 10-07-2010, 03:31 PM

I'm extracting information from a pivot table to get to the source data. When I do my extract I have 23 columns. Two of them need extensive work, and I'd like to know how to work them. I've attached a simplified example.

The column labled "Mos2" has some items I need to use as column headings in my current table: Budget, Enc/Pre-Enc, Projected, and the numbers 01-12, which are the Expenses, by month.

So, I would need 4 columns to be made, and each would have to line up with the amounts reflected in the "SumOfMONETARY_AMOUNT" column, but on the same lines the amounts orginally appeared on (since there are 22 other columns of data that apply to this same information). The columns would be Expense (with the months under it), then Budget, Enc/Pre-Enc, and Projected and then ideally also a Lapse column, which would be Budget - (Expensed + Enc/Pre-Enc)

The "01-12" would need to be converted to Months (01= January, etc).

I have ideas on how to do the 01-12, but no idea on how to do the rest. Any help would be greatly appreciated.

**rylo** · 10-07-2010, 06:38 PM

Hi

How about extending your example file to have >1 items, and produce an output sheet based on that data so we can see exactly what things should look like.

rylo

**jomili** · 10-08-2010, 08:31 AM

Sure. Sorry I didn't think of that before.

**jomili** · 10-08-2010, 10:56 AM

Sorry again. My last spreadsheet was way wrong. I hadn't had my coffee. Now I have.

**rylo** · 10-08-2010, 06:28 PM

Hi

Must be missing something here. You seem to have done a simple fix that, if the formula in column K is corrected, would give you what you require. So exactly what is it that you want done? Do you want a macro that will insert the columns and the formulas?

Also, in your original post you mention converting 0-12 into months. I don't see anything like that in the example file data or output.

rylo

**jomili** · 10-11-2010, 08:19 AM

Rylo,

Thanks for looking at it. No, I didn't do the conversion of the 1-12 to months in my example. They exist in column C, and I'd want them converted.

What I did in my example was to formulaicly create 4 columns for my Enc/Pre-Enc, PROJECTED, BUDGETed, and Expensed amounts. I'd like to be able to do all of that via a macro, AND within column C convert 01-12 to months.

**rylo** · 10-11-2010, 06:36 PM

Hi

Finally found the numbers in column C.

Try

Please Login or Register  to view this content.

rylo

**jomili** · 10-14-2010, 04:53 PM

I just wanted to pop in and express my apologies for not replying in so long. I've been pulled away on other projects (they're like a Hydra; you start chopping on one and two more grow in it's place).

Rylo, I haven't checked your code yet, but based on your past examples I have no doubts it will work wonderfully.

I'll try to get back to this, hopefully next week.