Complex Pivot Table

[rkspeaks]

Hi Faseeh,


I am stuck with a complex pivot table data. How can I analyze this in a meaningful format.

It contains Indian crop data for 9 crops, for a decade period, across all the states in India. It was requested by my neighbor uncle who is a scientist. Here is the link for the database. You can arrange, rearrange the same as long as the data is not disturbed when copying, pasting.


http://hotfile.com/dl/176400169/f225ca0/Indian_Crop_Data.xlsx.html


Thank you.

RK

 

[Faseeh]

Hi rkspeaks,


I just downloaded the file. It would be of great help if you ask Uncle what do he want to see. I mean he must have some questions in his mind that he want to answer from this data base.


An idea could be to simply plot it over year and select state, another could be to plot it over states and select year. etc. We can try to have map with diff. crops while map is showing quantity over them.


What do he want to see???


Waiting....


Faseeh

 

[rkspeaks]

Faseeh,


He want to see Statewise, Yearwise, Cropwise production without any hassle AND within a small sheet space without any complexity. I tried to give him a solution but the data for so many years is looks like a bit complex in arranging.


Thank you

RK

 

[Faseeh]

Ok Kindly give me some time and i will revert. Stay in touch...


Can i have the name of states these abbreviations are refering to??


Thanks,

Faseeh

 

[rkspeaks]

Here is your requirement.


State Code

Andhra Pradesh AP

Arunachal Pradesh AR

Assam AS

Bihar BR

Chhattisgarh CG

Delhi DL

Goa GA

Gujarat GJ

Haryana HR

Himachal Pradesh HP

Jammu & Kashmir JK

Jharkhand JS

Karnataka KA

Kerala KL

Madhya Pradesh MP

Maharashtra MH

Manipur MN

Meghalaya ML

Mizoram MZ

Nagaland NL

Orissa OR

Punjab PB

Rajasthan RJ

Sikkim SK

Tamil Nadu TN

Tripura TR

Uttarakhand UK

Uttar Pradesh UP

West Bengal WB

 

[nazmul_muneer]

Dear rkspeaks,

You can prepare this report by using Offset() that can change your data dynamically.

Type----

DA3=FY 2008

DA4=AR

DB3=Rice

DB4=Wheat

DB5=Maize

DB6=Corn

DB7=Spices

DB8=Tobacco

DB9=Chillies

DB10=Cotton

DB11=Sugar

DB12=G Nut

Now type the formula in DC3=

=SUM(OFFSET($B$1,MATCH($DA$4,$B$1:$B$30,0)-1,MATCH($DA$3,$B$1:$CX$1,0)-1+ROW(A1)-1,1,1))

copy down the formula.

Change Year and State. The data will change dynamically


Muneer

 

[nazmul_muneer]

rkspeaks,

You can prepare this report by using pivot table. For easily understanding you will have to rearrange the data.


Muneer

 

[rkspeaks]

Ok..Wait guys.. I am checking..

 

[rkspeaks]

Hey Muneer,


It kicks back saying you have entered too many arguments. Can you just check it out. Something wrong while pasting the post even if you have built the correct formula?


Waiting...


RK

 

[Haseeb A]

Hello RK,


You can use INDEX to re-arrange your data for Pivot table. See the file on SkyDrive.


http://sdrv.ms/Td7Bu2


Hope this helps,

Haseeb

 

[rkspeaks]

Hey Haseeb,


It's super! What I understand is that all the difficulty is in the arrangement of the data. If I want to re-arrange the data i.e. years, states over crop(crop is the filter), we have to rearrange it, right! So again you cannot escape from the rearranging process meaning Pivot table cannot arrange it for us. Can't it be a limitation of Pivot table?

 

[nazmul_muneer]

@rkspeaks,

I tested the formula. It's working. Anyway, you can also find the link below that has been solved by using Pivot Table.


https://hotfile.com/dl/176522260/96e4fe2/Indian_Crop_Data.xlsx.html


Regards,


Muneer

 

[Faseeh]

Hi rkspeaks,


Sorry for late reply, I really liked Haseeb's solution. Here is my solution:


http://dl.dropbox.com/u/60644346/Indian%20Crop%20Data.xlsx


Regards,

Faseeh

 

[rkspeaks]

Hey Faseeh,


Really it's a great spreadsheet work, You actually squeezed the data into a matchbox. I shown this to my neighbor uncle (who is a Scientist), he actually spellbound and immediately ordered me to convey his ton of thanks to you. He even appreciated Haseeb's work as well. Having not exposed to all these technology advancements especially to MS Excel application, he delighted by your work. You actually made him to know the power of Excel.


But he was very quick and found some small flaw in that, for some of the states (TN-Tamilnadu, GJ-Gujarat) the data labels(which were on the face of the map) were not linked to the data it seems. I tried to poke at it and link them to data. But I got a doubt, Is it like I have to link all these labels for all the missing states, for all the years and across all crops or with a small magical adjustment it will be changed ?? Otherwise it is nice work.


Thank you very much as it is a lot of learning today morning.


Waiting...


RK

 

[Faseeh]

Hi rkspeaks,


Thank You for your complements. I picked a map from internet showing Indian states and for each made a text box linked to the corresponding percentage cell. So it was an activity done only once. When you will select the Crop & Year, the data will change automatically and hence the values for %age and in text boxes will change.


Its just my unfamiliarity with map that has caused this. Correct it once and it will be done.


Regards,

Faseeh