Raw Date in a Grid Format - How to Convert to Pivot-Friendly Format

[GMoney]

Hello Excellers!


I have a problem that rears its head from time to time when we get an export of data from our finance team. Their system exports data in a grid format, which is useless for further analysis e.g. pivot tables. Here is a simple of example of what I mean. The data comes out in this format;


Apples Apples Bananas Bananas

Shop Type Shop Name Q1 Q2 Q1 Q2

Supermarket Woolworths 63 9 21 74

Supermarket Coles 96 99 81 97

Supermarket IGA 62 14 5 81

Grocer Mr Smith's Fruit 73 95 65 62

Grocer Fruit 'n' Things 57 13 49 94

Grocer Tuity Fruity 43 91 85 82


But for further analysis we need it in this form


Shop Type Shop Name Fruit Quarter Sales

Supermarket Woolworths Apples Q1 63

Supermarket Woolworths Apples Q2 9

Supermarket Woolworths Bananas Q1 21

Supermarket Woolworths Bananas Q2 74

Supermarket Coles Apples Q1 96

Supermarket Coles Apples Q2 99

Supermarket Coles Bananas Q1 81

Supermarket Coles Bananas Q2 97

...


The example does not look so clear in this text box but I can send in Excel. And obviously in this small example it is trivial to manually copy and paste, but when there are tens of thousands of rows and tens of columns then that is not an option.


I did Chandoo's VBA course and made several attempts at write some generic code to carry out conversions like this to any data set, but I could not make it work.


Does anybody have any suggestions?


Cheers,


Graham

 

[NARAYANK991]

Hi Graham ,


The sample you have posted does not seem to follow any standard format ; is this so in practice ?


If not , can you upload a proper sample , so that we can see whether formulae alone will do the job , or VBA is required ?


Narayan

 

[SirJB7]

Hi, GMoney!

Check this:

http://www.2shared.com/file/ivDvQvDA/Raw_Date_in_a_Grid_Format_-_Ho.html

Just press Alt-F8, then select and run the macro "FireFinancialTeamGuys".

Regards!