Converting a report into a Pivot Table format
 

Hi all,

I desperately need to convert a report I receive every month into a
pivot table format.
Here is the table:

Country Jan Feb Mar Apr May June ..... Dec
France 2 3 4 5 6 7
Spain 8 9 10 11 12 13 etc
etc

I want to convert this into a pivot table format like:

Country Month Value
France Jan 2
France Feb 3
Spain Sept etc

The list of countries is around 170 lines long.
I have 13 month (12 months + total)

I have to do the same with several reports, however each report is
built on some variables like:
- Year
- Data type (Actual, Budget, etc)
- Product (Bananas, Apples, Cherries, etc)
- Sub-Product (American Bananas, Australian Bananas, Indian Cherries,
etc)
- Currency (Dollar, Euro, etc)

The reason why I take by currency is to calculate any FX impact on the
revenue.
The reason I take it by product (and sub product) is to be able to see
it both from product view or country view.

It would be great if in adittion to converting the existind data on the
table, I could also enter on a few popup questions the variables I used
(currency, product, etc) and that these got repeated on some extra
columns so when I merge all the different reports I know which data
relates to what report.

Can anyone please help me convert this table?


Thanks in advance


Leo

Converting a report into a Pivot Table format
 

Sub ReorientData()
Dim lastrow as Long, i as Long
lastrow = cells(rows.count,1).end(xlup)
for i = lastrow to 2 step -1
cells(i+1,1).Resize(12,1).EntireRow.Insert
cells(i,2).Resize(1,13).copy
cells(i,3).Pastespecial xlValues, Transpose:=True
cells(1,2).Resize(1,13).copy
cells(i,2).Pastespecial xlValues, Transpose:=True
Next
Range("D1").Resize(1,11).Entirecolumn.Delete
Range("C1").value = "Amount"
Range("B1").Value = "Month"
End Sub

Untested. Test on a copy of your data.

--
Regards,
Tom Ogilvy




" wrote:

Hi all,

I desperately need to convert a report I receive every month into a
pivot table format.
Here is the table:

Country Jan Feb Mar Apr May June ..... Dec
France 2 3 4 5 6 7
Spain 8 9 10 11 12 13 etc
etc

I want to convert this into a pivot table format like:

Country Month Value
France Jan 2
France Feb 3
Spain Sept etc

The list of countries is around 170 lines long.
I have 13 month (12 months + total)

I have to do the same with several reports, however each report is
built on some variables like:
- Year
- Data type (Actual, Budget, etc)
- Product (Bananas, Apples, Cherries, etc)
- Sub-Product (American Bananas, Australian Bananas, Indian Cherries,
etc)
- Currency (Dollar, Euro, etc)

The reason why I take by currency is to calculate any FX impact on the
revenue.
The reason I take it by product (and sub product) is to be able to see
it both from product view or country view.

It would be great if in adittion to converting the existind data on the
table, I could also enter on a few popup questions the variables I used
(currency, product, etc) and that these got repeated on some extra
columns so when I merge all the different reports I know which data
relates to what report.

Can anyone please help me convert this table?


Thanks in advance


Leo