Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Top 6 Reason Codes with Top 3 Stores using VBA

Posted on 2014-01-09
33
Medium Priority
?
234 Views
Last Modified: 2014-02-10
I had some previous assistance in getting the top 6 reason code and top 3 stores, however the code is not working fully and I have some additional criteria that was not provided at the time that I submitted the first request. In the attached workbook the first tab is a summary of the data tabs that follow it.

On the first chart (Schedule Delay) - I need to  1st provide the top 6 reason codes based on the pie chart and then the top 3 stores that have the highest number of days.  This VBA is provided (Top6Top3 module) however it is not aligning under the first pie charts, the percentages are off, and the small box numbers are not pulling forward.  

The second chart (Store Complete) - The data comes from the Complete tab and pivot table info is on PIVOTS tab. Criteria for Top 6 all reason code in the pie chart that are 5% and over and Reason Code is not 100-Comp.  This is not a top 6 put such a list of the stores as we don't have any values to rank the stores.

The third chart (Cost Variance) - The data comes from the Budget tab and pivot table info is on the PIVOTS tab.  Criteria for Top 6 all reason codes in the pie chart that are 5% and over and Reason Code is not ON-BUDGET.  Top 3 is the stores with the highest variance %.

Please let me know if you have any additional questions that I have not addressed.
EE-Test-Scorecard-2014.01.09.xlsm
0
Comment
Question by:jmac001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 15
33 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39769690
Hi
Are the labels filled with the correct data under each chart ?
gowflow
0
 

Author Comment

by:jmac001
ID: 39771462
Hi Gowflow,

Here is an revised workbook, there some minor updates where the data changed.
EE-Test-Scorecard-2014.01.09.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39771981
I appreciate you attached the latest workbook that is fine. But still I do not appreciate when my questions are not answered ! This build frustration. I ask the question again:

Are the labels filled with the correct data under each chart ?

Gowflow
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jmac001
ID: 39772163
Sorry for the miscommunication in answering your questions, based on the last workbook that I uploaded the labels match what is under each chart.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39772453
ok fine. Let me work on all this, as I see from your initial post that the source for each chart is not the same hence we cannot adopt a similar approach of calculation for each chart. Each one is an own entity and need its own variables.

Am I right ? If not pls correct my understanding.

I would like to please summarise in 3 lines what are the input sheet and data tables for each pie.

Like pie1 (the left most) picks data from sheet X and from table Y ...
pie2 center etc...
pie3 right most etc ...


Rgds/gowflow
0
 

Author Comment

by:jmac001
ID: 39772583
Correct each one has it own variables

Pie 1(left) - picks data from sheet - RCSupport and OnTimeCals; pivot table from RCSupport
Pie 2 (center) - picks data from sheet -  Complete; pivot table from PIVOTS
Pie 3 (right) - picks data from sheet - Budget; pivot table from PIVOTS

Let me know if you have any additional questions. Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39772656
Yes the last post is GREAT !!! this is the type of info to the point that I need.

You mentioned somewhere in your question post that pie 2 has no top6 and stores or something like that ?? but aren't we getting top6 and top3 for all the graphs ?

If there is exceptions pls clearly state for each pie what you expect in the labels. short and sweet.

gowflow
0
 

Author Comment

by:jmac001
ID: 39776508
Pie Chart 1 - Top 6, Top 3
Pie Chart 2 - Top 6, List of store names
Pie Chart 3 - Top 6, Top 3
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39776657
ok fine let me work on all this but you will need to be a bit patient but hopefully will pull this thru !!! :)
Regards/gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39783140
ok here it is:

I hope we can have a quick and final exchange. Just a small note, unfortunately the past time you had raised this question of top6 and top3 I had posted a reply but it seemed you used an old version reason why you had wrong figures. Anyway I worked on all 3 graphs as per your explanation I will ask you to go thru the figures and check each one.

The charts are:
Chart1 left most
Chart2 Center
Chart3 Rightmost

I would suggest we go Chart by Chart.

Do the following download this version and what I did is I pushed further down your data labels so you can have them as referential. Activate the Macro Top6Top3 and see the results.

Take figures for Chart1 and check all of them and if you see discrepancy with your figures then check in the table to make sure either you were wrong or if I am wrong then you tell me what it should do and what I missed.

After you finish with Chart1 please post the reply so will work chart by chart.

Regards
gowflow
EE-Test-Scorecard-2014.01.15.xlsm
0
 

Author Comment

by:jmac001
ID: 39786767
Thanks, will have feedback for you tomorrow.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39786782
ok fine no problem
gowflow
0
 

Author Comment

by:jmac001
ID: 39789810
So I tested chart 1 in the workbook that I downloaded from you. Top 3 did not generate correctly, please see the attached.
EE-Test-Scorecard-2014.01.15-cha.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39789952
Where are your comments as per my request ?? I could see it is not correct right from the beginning I need from you how you get to your figures in details !!! like what divide by what multiply by what etc....

ARE WE BACK TO SQUARE ONE in no understanding !!!!
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39790010
try this file
gowflow
EE-Test-Scorecard-2014.01.18.xlsm
0
 

Author Comment

by:jmac001
ID: 39798512
Hi,

Tested the first chart again.  The top 6 info is correct and I validated by getting the % from the pivot table on the RC Support tab.

The top 3 is bring back the incorrect data, only the first box has the correct top 3 however the number of days is incorrect. Validated by creating a pivot table with the data on the OnTimeCals tab.  The manual check can be found on the OnTimeTables.

Please let me know if you need any additional info.
EE-Test-Scorecard-2014.01.18-Res.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39799715
Ok here are my findings:

1) I noticed that the problem we have is that when the macro reaches the point that it need to sort the sheet OnTimeCals for each Top6 to find the top3 stores the sheet does not sort properly. It is maybe due to mixture of formulas that makes the sorting goes wrong.

2) SO to try to overcome this and to troubleshoot, I made a copy of this sheet into a new sheet called OnTimeCals2 and pasted the values so we do not have formulas.

3) I modified the macro for graph1 to look for OnTimeCals2 instead of OnTimeCals and here are the results in the attached file.

4) I noticed that the values for the store top3 comes out correct however the description is wrong. SO my question to you how are your pivot tables made what is the Column that you look for for store name isn't is Col B labeled Store Name in sheet OnTimeCals ??? If yes then I am afraid that your pivot also are giving you the wrong info !!!

So please check the data in this chart and for me it seems it is correct. But if you see a different way of doing please explain it to me.

Rgds/gowflow
EE-Test-Scorecard-2014.01.22.xlsm
0
 

Author Comment

by:jmac001
ID: 39803484
Hi gowflow,

1. Do you have a recommendation on how I can work around the formulas in the OnTimeCals tab since they are proving to be an issue? May be copy the data from OnTimeCals to OnTimeCals2 with VBA?

2. I refreshed the pivot table and came up with the same results when the macro is run.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39803635
YES I can do the copy in VBA that is no sweat, but first I need you to confirm are my results via OnTimeCals2 Correct ??? Like you had previous wrong results and we found something new ?? I am not clear on what is right from wrong.

What do you mean by:

2. I refreshed the pivot table and came up with the same results when the macro is run.

gowflow
0
 

Author Comment

by:jmac001
ID: 39804218
Yes, the results that you have in the OnTimeCals2 tab are correct. Verified that I received the same results in the tables on the OnTimeTable tabs using the data from the OnTimeCals tab.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39804274
Yes but then why the store names are wrong ??? they do not match the data that are in the black labels that you created this is where I am confused.
gowflow
0
 

Author Comment

by:jmac001
ID: 39804568
When I was troubleshooting I noticed that two of the calculations was looking at the wrong line so everything got shifted by a couple of lines so it shifted numbers to the wrong store.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39804678
and ... bottom line ? Mine are correct ? all correct ?
gowflow
0
 

Author Comment

by:jmac001
ID: 39805046
Bottom line all of yours is correct
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39805756
ok fine.

Here is my proposal:

AS this is getting way too complex, Below is my solution for Graph1. Please test it and if you like it I suggest you close this question and open a related one so we can move to Graph2 and please in the draft of the next question pls specify your request for the second graph as to the sheets to use and if top6, top3 ? this small details that would help us minimize the wasted time.

Regards/gowflow
EE-Test-Scorecard-2014.01.24.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39813582
Did you have a chance to test it ? Please let me know on how you would like to proceed.
gowflow
0
 

Author Comment

by:jmac001
ID: 39815794
Looking over, if okay will close this request and open a new one for chart #2
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39817700
great !!! pls don't forget to put is short and sweet in the question with a link in here so I can be notified.

I hope we are now on the right track ... after looong working on demystifying all this !
:)

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39830046
Any news on this issue ? No new question posted ? this one still open ! Do you have any problem with this ? Did I miss your post ?
Let me know

gowflow
0
 

Author Comment

by:jmac001
ID: 39834302
Hi gowflow,

I tested in the sample file that we have been working with and everything looks good.  Do you have any recommendation on how I can troubleshot my live file it is erroring on the

 '---> Sort the Table by #Days Reverse
    TblAddress.Sort key1:=WSPivot.Range(StTbl).Offset(0, 2), order1:=xlDescending, Header:=xlGuess

Open in new window

?

Thanks
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39834638
yes you need to tell me where the pivot table start give me the reference address like the cell address of the leftmost corner of it the best is you to put a snaptshot of your pivot table and I will tell you what cell.

Now that we hv this issue I noticed that you move around pivots and this will make the code unstable. You need to keep the pivots at the same location all the time or else then you will run into similar issues.

gowflow
0
 

Author Comment

by:jmac001
ID: 39847667
Retested and it is now working 100% in the live version of the report, not sure what was going on last week.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question