Pie, Table and Top 3

I see that your chart pulls its info from
rcdata sheet. can't we look in there to see your top 6 / top3 ? if yes then what column determin this top and then it is a small piece of cake from there on.

gowflow

The RCData is a summary of the data and does give the top 6, however it does not give the top 3.  Attaching a sample of the rest of the data that is behind the pivot tables
Sample-RC-Pie-EE-10.07.xlsm

Could you be more clear if you need help we cannot guess and start asking 1 zillion question
Your top 6 or top 3 how do you get them give me a clear sample based on the data you posted say you take from sheet XX the column a divide by col z multiply by .... then you get a figure this for all records is ranked either from biggest to lowest and that should give the top ....


Something clear like this.
gowflow

Hi as I mentioned in the initial post.  I am taking the top 6 largest slices from the pie chart which is populated for the data in RCData which is taken from the Data sheet in the sample date.  

Once that is completed I then create a pivot table based on the Data sheet to get the name of all the stores for the top 6 RCs and then manually place the top three into the table that is underneath the same pie chart that I uploaded in the sample file.
Sample-RC-Pie-EE-10.07-v3.xlsm

I am terribly sorry but I do not understand your request. nor understand how these sheets are interacting altogether.

UNLESS

you can give me a clear example with figures so I can understand how your pie chart is construed, I am afraid I can not help you any further.

good luck
gowflow

Excel 2010, Tables, PivotTable, PivotChart
We need the raw data behind the pseudo PivotTables on your sheet "Data".
I generated some fictitious raw data in my "Table1".
http://www.mediafire.com/download/ljrgdkbks7w2rv5/10_08_13.xlsx

gowflow - I am trying to find a way to cut out the manual update of the top 3 as this is going going to be a weekly report for three different division and and there will be multiple charts that I will need to identify the top 3 based on the slices in the pie chart.

I apologize the data tab did not save in the last up load.  Attaching file again.

Herebs7 the sample you provided is along the lines of what I am looking for, however I don't know if that is going to be attainable based on how the RC is in the data. I don't need to place the info in a chart but some type of table format that would include the info from the pie chart --- Name, Value and Percentage.
Sample-RC-Pie-EE-10.07-v3.xlsm

In the file you have no sheet called Data but DataII and DataSource which sheet you refer to ? and again please provide a clear example based on the data that is the said sheet what column for you indicate the value from which you can conclude top ?

gowflow

For the Pie Chart - Evaluation of the top 6 slices

The detailed data on the DataSource is populated in summary format to the RCData tab.  
See calculation in C2 on RCData tab.  Pivot created from this and pie chart is created. Get the top 6 based on the percentage. Write down the label name, value and percentage in the boxed created under the pie.

For the table top 3 of each of the top 6 slices
1. Using the DataSource tab, created a sum of the RC per store # on DataII (columns X-AW).  Calculation is in X2.
2. Create a pivot table for each of the top 6
Sample-RC-Pie-EE-10.08.xlsm

Excel 2010, Tables, PivotTables, PivotChart
Thanks for the raw data in sheet "DataSource"
I used nothing else but this data.
No macro.
PowerPivot would be more appropriate for this kind of job,
if you are familiar with it.
http://www.mediafire.com/download/yyau19muh2gyia6/10_08_13.xlsm

thank you for your info that is a bit more clearer now but still not sure I understand fully. I looked at the tables and read your instructions pls confirm if my understanding is correct in the data you have the last attached file is it correct to assume that the below are ranked from 1 to 6 ???

1- LLD-POSS      15      657
2- PAR-CONT      14      597
3- PAR-SITE      7      316
4- PAR-CUST      5      226
5- LLD-APRV      4      193
6- PAR-OSM      4      163

So for me is it correct to say that these are the top6  and the top3 are the first 3 ?
gowflow

GowFlow,
I used the raw data on sheet "DataSource" exclusively.
This data does not jibe with all the rest of the sheets.
If the OP reveals the full raw data of her first attachment,
we might be able to reconcile the results.
My conversion of the raw data to Table10 is a bit convoluted,
maybe you can come up with an elegant macro
or another way to de-normalize the database.
Regards,
Herb

Herb
I do not follow you !! You post links outside of EE that I do not dare to look for as so many risky sites.

If you have a solution or a proposal pls post it clearly in here.
gowflow

gowflow you are correct in the Top 6 RC - To get the top 3 stores for the Top 6 RC need to go back to the data to pull that out. I am doing this manually. Looking for an option where there is a lesser chance of human error when inputting the data on the report.

 To get the top 3 stores for the Top 6 RC need to go back to the data to pull that out. I am doing this manually. Looking for an option where there is a lesser chance of human error when inputting the data on the report.

Do not mean to be sarcastic !!! but this is exactly what I have been struggling with you since the very beginning of this question. I will repeat my question again but for the last time and please hear me well:

HOW DO YOU CALCULATE YOUR TOP3 ??? WHAT SHEET WHAT COLUMN WHAT FORMULA ? PLEASE GIVE ME CLEAR EXAMPLE THEN AND ONLY THEN I CAN BUILD YOU A MACRO

Clear ?

gowflow

Gowflow I did provide this info in the last post made yesterday:

For the table top 3 of each of the top 6 slices
1. Using the DataSource tab, created a sum of the RC per store # on DataII (columns X-AW).  Calculation is in X2.
2. Create a pivot table for each of the top 6... which I provided a sample on the DataII tab.

Sorry I am slow !!!!

yes you did provide this but also you expect people to do the job that you know on your tip and by heart closing your eyes exactly as you do

Well reality is NO

People are not as intelligent and clever as you are.

I am good at programing not good at your job so please

take me to my stupid level and take a clear example and give me all the clear details of exactly what you do and what column yo add and what total you get in all the details as it is not clear to me at all

gowflow

if you have no patient for explaining things in details I would understand.
gowflow

Here is a pdf file of my work.
http://www.mediafire.com/download/gmmj3pp8q52x2i5/10_08_13.pdf

Herbds7

pls post the file here. We have no intention on clicking on any external website except the fairly well known and secure like Microsoft and the like.

and this with all due respect with the website you refer to. This comment is my own opinion and does not involve or imply any other entity but myself.
gowflow

Herbds7 - i will take a look at what you proposed not  familiar with power pivots

jmac001

If you need my help, I can only assist in a vba solution in the lines of my post ID: 39559708 as with the explanations you provided I cannot get the figures to match.

Rgds/gowflow

gowflow,

The numbers are not going to come out actually as what is in the pie chart in order to convey what I was looking for I took a sample of the data and desenatized it.  Please let me know if this is going to be an issue.

ok let me rephrase my request

When you have your data and pie and need to get your top 6 and top 3 you go and do a certain calculation or certain comparisons that lead you to identify the top6 and in each their top 3.

so,

I need to know what is this process or calculation that you do exactly in detail giving me a clear example so I can build this calculation into an automation routine and then and only then the system could help you automate this task and save you time. Did I explain myself better ?
gowflow

PS what do you mean by 'desenatized' a new word for me, means what ?

still need help on this one ?
gowflow

Gowflow yes I still need help with this one.....

To answer your questions.  Its all visual....I look at the pie that is created and take the top 6 RC by percentage.  Once I have that I then do a pivot table on the DataSource to get the actual store info for the top 3.  I hope this answers your question.

Desenatized - I cleaned the data and removed anything that was important such as names out of the file that was sent.

Once I have that I then do a pivot table on the DataSource to get the actual store info for the top 3.

This is exactly what I need. How do you get the top 3 stores please give me here an example so I can relate to the data in datasource is that so difficult to understand ? I have been asking for this since the very beginning but seems you don't believe that I can give you an automate solution !!!

TRY ME !!!
gowflow

If you look at the sample excel that attached to ID 39557213 on the tab labeled Data II, This is the data that is being used to create the top 3.

ok fine lets take a clear example in sheet Data II you have a pivot for LLD-POSS

Row Labels      Sum of LLD-POSS      Ranking
XXXXXX1      576      576
XXXXXX4      258      258
XXXXXX3      34      34
XXXXXX7      0      0
XXXXXX6      0      0
XXXXXX9      0      0
XXXXXX8      0      0
XXXXXX2      0      0
XXXXXX5      0      0

so I presume the top 3 are 576, 258 and 34
and if correct then in the Graph in the first label you have

Landlord- Possession, 15d                               23%
xxxxxxx1 - 135 days
xxxxxxx2- 111 days
xxxxxxx3- 105 days

the 23% I see it is the result in sheet RCData 657 divide by 2778 equal 23.65% which pls confirm it is correct.

I cannot seem to get the figure of 135, 111 and 105 days which need you to advise how you get these figures ? from what divide by what ???

On an other issue the last question you posted which you just closed yesterday (TrackingNo) I posted a comment did you see it ? I am not so sure the answer you accepted is the correct one in all cases.

gowflow

Sorry about the discrepancy in the numbers they changed when I created the example. That number is just sum of the number of days delay found in the data on the DataSource tab. For the pivot you are looking at LLD-POSS is column "AF" of the DataII tab.

Yes I did see you, post but have not been able to test.  So far the first solution is working could you give an example that would cause the first one not to work.

Please so much time wasted for data mismatch let's sum it up
You mean for the file you posted then it should be:

LLD_POSS
Landlord- Possession, 15d                               23%
xxxxxxx1 - 576 days
xxxxxxx2- 258 days
xxxxxxx3- 34 days

and not as it is showing on the graph as follows:

LLD_POSS
Landlord- Possession, 15d                               23%
xxxxxxx1 - 135 days
xxxxxxx2- 111 days
xxxxxxx3- 105 days


I would appreciate your earliest reply as this is dragging so long.

Re the other question I will look at it but in principle the answer is wrong to put the StoreN as counter !

gowflow

An other question:
Can I assume that you will always have the 2 following sheets in the same format (Columns and Row start) as they are posted in this workbook for both sheets:
RCData
DataII

Like you will always have these 2 sheets ?? as they contain crucial data to be able to give you want you want.

I need an answer on this and the previous question afterward I can build you a macro that will automate the work HOPEFULLY !!!

gowflow

Yes for the file that I posted the data that you have listed is correct.  As for your second question yes the data is always going to be on the two sheets as indicated.

ok here it is. I hope this is what you are looking for.

Load the file and activate macros.
In sheet Chart you will find a button called: Create Top6 and Top3 Labels
Activate it
Check the results below the graph

Let me know.
gowflow
Sample-RC-Pie-EE-10.08.xlsm

Hi gowflow,

Thank you so much for the code.  I am still looking at the result of when the code is ran but found this issue so far....  I will have a total of three charts, side by side and the code right now is placing the textboxes under the last chart.  How would I place the box under the first chart which spans columns A:L (rows 20:48 if needed)?

Also, is it possible to trim the text of the top 3 - format will be  name (XXX)-XXXX would like to remove (XXX)-XXXX ?

Will continue to work through the results and let you know if I find anything else.

Thanks

Sorry for the delay in replying as I was out travelling.

Just saw your reply and if the code works like the question you posted on 1 pie I believe you should proceed and close this question as you feel appropriate.

If you believe you need help further in getting the code to work on more than 1 pie or other criteria then you are welcomed to post a related question and let me know of the question by putting a link in here and I will be glad to assist you.

Rgds/gowflow

Any chance to have checked my last comment ?
gowflow

The alignment is not correct for the 1 pie chart.  It is offset the top three are under the chart and the bottom 3 are off set to the left of the top three.  The number inside of the box is hidden behide the box or to the right of the box.   Also after further testing the top 3 is not is not calculating the number of days it is zero.  Working to provide an example without proprietary info in it.

ok will wait for your example as the file I posted worked correctly. Maybe you have changed something in between especially that you mentioned more than 1 pie maybe this is the problem that is changing the display. I will need to see a sample as just explanation does not help me reproduce the problem so I could correct it.
gowflow

Any news ? Why does it take you ages to reply ? It is kind of annoying when someone provide help and work for you don't you think ?
gowflow

I apoligize, I got pulled onto another project.  I am working to get the data so that I can show the errors.

Well if you are concerned about getting a solution to this one, I think you will need to devote some 'follow-up' time as well.
gowflow

I was working to get you a file that would be most respesentive of the file I was using so that you could see how the data was populating. So attached is a workbook set up just like the file I am using with the data scrubbed.  
1. You will see that the top 6 did not populate correctly and the percentages are off.  
2. The second line is offset and starts under the second pie chart and does not align with the first line (of 3).
3. The numbers to the boxes are to the right of the box and to the back.
EE-Test-VSBA-Scorecard-2013.11.0.xlsm

Sorry but from where you get your data for the other 2 pies ????

I gave you a solution based on the sample you provided which was 1 pie at that time with specific sheets and it was a 100% full working solution.

You come back saying that you installed it on several pies and the data came out wrong and 1 month later you give me a total different layout and expect the solution brought first to simply work ????

I am totally sorry, but this is not serious.

I suggest that you proceed to accept this question as a solution that I provided to you on ID: 39603363 on 27/10/2013 that answered exactly your question here.

You are welcomed to post a new question with the file you attached now explaining where you got your data for each of the other 2 pies and then I will be glad to assist in this frame of work.

gowflow

I would be happy to accept the solution if the solution was working as I pointed out in the attachment the percentage is off, the box are not aligned and numbers in the boxes are not aligned.  I never stated that I was using this on multiple pie charts I have been testing on the same pie chart and set of information that I sent from the beginning. I said that due to the other pie charts the boxes where not aligning under the correct pie chart.

I am not asking you to address the other pies in the worksheet and I have no problem with opening a new case to address the other pie charts. I would just like it to work on the one that I asked for help on.

I gave you a 100% working solution for the file you posted in the codiitions that you posted and yet you rate this question as B

May I ask why ?

This is not a smart way to get help from Experts !
gowflow

Gowflow I am sorry that I had to go with B, but the solution did not work 100% as I tried to explain the boxes were not aligning for me and that the numbers in the boxes were behind the box and two the side.  I got frustrated in my attempt to explain this problem to you.

I understand based on the last attachment that I submitted that it was total different from the original snapshot of the report, but it is a work in progress and those were the additions that had been made in the time that I had been working with you.  I figured if I had the code for the first chart that I could modify to include the 2nd two charts.

I understand based on the last attachment that I submitted that it was total different from the original snapshot of the report, but it is a work in progress and those were the additions that had been made in the time that I had been working with you.  I figured if I had the code for the first chart that I could modify to include the 2nd two charts.

Well you figured that you could add .... well it is not that obvious isn't it. You are not a fair person and to give you the code I already gave you was a hell of a lot of work.

Being an other person, I would have loved to get you the solution for all your graphs. But unfortunately one cannot buy niceness.

god luck
gowflow