Convert Excel Rows in Columns

[robin786]

I am using Excel 2007. I have monthly sales data for various countries displayed in Rows as follows. Sales are in column C. Country is in Column A

USA January 10
Canada January 20
UK January 30
Italy January 40
USA February 100
Canada February 200
UK February 300
Italy February 400
USA March 1000
Canada March 2000
UK March 3000
The monthly sales data that used to be displayed in rows should now be displayed in columns as shown below. Also, some of the country sales occured in one month may or may not occur in the next month. Eg:- Italy is missing March data

January February March
USA 10 100 1000
Canada 20 200 2000
UK 30 300 3000
Italy 40 400 0
I need data like this to be displayed in another Excel sheet in the same work book. I need an Excel VBA code for this. Kindly suggest.

 

[SkipVought]

hi,

This is what Pivot Tables are all about.

HOWEVER....

your months must be REAL DATES. January, February, etc will not collate in the month order.

I would suggest that you mass change January to 1/1/2014 or whatever year is applicable, etc for each of the 12 months. THEN select the date range and change the NUMBER FORMAT to CUSTOM [highlight #FCAF3E]mmmm[/highlight] which will display full month name.

Then pivot your data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Actually, Excel figures out that your month strings are intended to be sorted in month order and not alpha order.

So you don't really need to change January to 1/1/2014, for instance.

However, in general, it is best to use actual dates. What would happen in this table, if you wanted to include data for more than one year, in order to see year ro year trends, for instance? You would be up the creek. Use REAL DATES!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]