Solved

Top 6 Reason Codes with Top 3 Stores using VBA

Posted on 2014-01-09
33
190 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
  • 18
  • 15
33 Comments
 
LVL 29

Expert Comment

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

Author Comment

by:jmac001
Comment Utility
Hi Gowflow,

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

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:jmac001
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
Thanks, will have feedback for you tomorrow.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine no problem
gowflow
0
 

Author Comment

by:jmac001
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
try this file
gowflow
EE-Test-Scorecard-2014.01.18.xlsm
0
 

Author Comment

by:jmac001
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
and ... bottom line ? Mine are correct ? all correct ?
gowflow
0
 

Author Comment

by:jmac001
Comment Utility
Bottom line all of yours is correct
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
Looking over, if okay will close this request and open a new one for chart #2
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
Retested and it is now working 100% in the live version of the report, not sure what was going on last week.
0
 

Author Comment

by:jmac001
Comment Utility
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now