• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Part 2 - Chart 2 Top 6 and List of Stores


Tested Chart 2 and the Reason Code Name, # days, and % are all the same.  The labeling for the Top6 is the reason code name, #s, %. #s represents the number of stores.  

Chart 2 is the top 6 reason codes from the pie chart Stores Complete at Open. For each of the Top 6 reason codes show the store name with the reason code in the data.   Pivot table data located on the PIVOTS tab, the data is found on the Complete tab.

Please let me know if any additional information is needed.
  • 10
  • 7
  • 3
1 Solution
Hi jmac001,

Is this a query from your side or a kind of knowledge sharing?  Thanks.

jmac001Author Commented:
It is a query, I've been working with gowflow on getting the information to populate using VBA.
Okay.. what is your query - can you explain it in detail?  Thanks

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

ok pls lets recap what is needed for Chart2

1) top 6 same as Chart1 ?
2) Top3 stores same as Chart1 ? if yes state it. if no pls advise what is needed then referring to an example on a table and sheet. as cannot relate the data that you manually inputed in black cannot find the source.

jmac001Author Commented:
Chart 2 is:
1.) Top 6 same as Chart 1
2.) Just a list of the stores by name for each Top 6
3,) Data Source is on the Complete Tab
4. The pivot table is on the PIVOTS tab
Well you are a pro in delays and in lack of information.

I am looking close at Chart2 and I see major diffrences with Chart1

1) You said to use table PIVOTS (so I presume it is the table that has a title COMPLETE)
2) You said to use the Data table Complete. This table has not the same format as the previous OntimeCals in Chart1 which had the store names on Row1 this one has no store name in Row1 whatsoever it has in Col B the Store Name
3) If you look at the PIIVOT sheet pivot table COMPLETE
0                      41
100-COMP      31
MST-VNSHP      10
PAR-OSM      9
PAR-CONT      5
MST-EXTLC.      1
MST-TOFF      1
SDC-DESN      1
Grand Total   99

what is the item 0 ? then item 100-COMP does not exist in sheet Complete also store MST-VNSHP does not exist in Sheet Complete

Can you please explain what do we need to extract and how with an example.

The faster you reply the faster you will get an answer and the faster the Expert working on your issue can have all the details fresh. As when time passes it takes more time to remember the whole logic and then you have less chances to get an accurate response.

I completely agree with GowFlow.

Any news jmac001 ?
Try to be quick this time !!!


It seems you are either busy or ... ? can't explain these delays in replying. Sorry to push but if your interested in a solution you need to improve the speed of replies.

jmac001Author Commented:
1. Yes the table listed Complete is the correct Pivot on the PIVOT Tab
2. You are correct the formatting is not the same as the OnTime Cals tab, however the store name is in the same column (B) in each of the tabs
3.  The filter for Status is looking at all records and not just those that are OPN, so those the 0 - 41 would be stores with no reason codes populate.   100-COMP and MST VNSHP are both on the Complete tab in and appear in Columns J (Reason Code) and K (Reason Code 2).  

The information that needs to be extracted is the information from Pie Chart 2 - Store Complete at Open for the Top 6 reason codes by percentage excluding 100-COMP.  

The pie chart is populated from the Complete pivot table on the PIVOT table using the data from the Complete tab specifcially the Reason Code 2 field.  

Based on the spreadsheet attached the first reason code would be :

Mast-Vendor/Shopfitter, 10s  17%
IBN -52957
Ok thank you for replying, I have these question:

 I see the logic now is totally different and you introduce Reason code that were not there before. Now with your explanation I can see some light at the end of the tunnel however I still have questions:

1) Shall we ALWAYS ignore in the PIVOT (for this Chart obviously) items that are
?? As you mentioned that the first item would be MST-VNSHP so I assumed and you explained it that we need to ignore the 0 and 100-COMP
is that correct ??

2) What is the 0 -41 that you refer to in your post 3. ??? I need to know what are the items that you do not want to take in consideration in all cases not specifically now in this example as the routine could work now and fail later. My feeling is that you should probably have a certain criteria in the Complete table that you filter by to exclude certain items and this is the criteria I need. Remember I need to replicate exactly how you do it manually, so we come up with the same results.

3) I assume then that we need to SORT the table complete by the Col K that is Reason Code 2  will this be ALWAYS the case ? Like always will sort by Col K???

Please answer as fast as possible to keep the momentum going as the whole thing now is fresh. It took me long to be able to dissect all this and remember we do not know your business you know it so if you don't provide the smaller details you will end up not getting a solution.

Having not answered, I assumed the following:

1) Yes
2) No idea still need to know.
3) Yes

Pls try this version and let me know if it give the expected results for Chart2. This is the Code for Chart2

Sub Chart2(WSChart As Worksheet, ChrtIndex As Integer)
Dim WSPivot As Worksheet
Dim WSData As Worksheet
Dim WSDataTemp As Worksheet
Dim WSDesc As Worksheet
Dim Tbl As PivotTable
Dim TblAddress As Range
Dim StTbl As String, EndTbl As String, LeftLabel As Integer, TopLabel As Integer, lenBold As Integer
Dim Code As String, CodeDesc As String, ColCode As Integer, AvgDays As Integer, PercOpen As Double
Dim I As Long
Dim cCell As Range
Dim Sh As Shape
Dim Group(12)

Set WSPivot = Sheets("Pivots")
Set WSDesc = Sheets("RCSupport")
Set WSDataTemp = Sheets("Complete")

'---> Create New WS copy of Data pasted as values
On Error Resume Next
Set WSData = Sheets("Temp")
If Err <> 0 Then
    '---> Sheet Does Not Exist, Create it
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Set WSData = Worksheets(Worksheets.Count)
    WSData.Name = "Temp"
End If
On Error GoTo 0
WSData.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

'---> Get Pivot in WSPivot
For Each Tbl In WSPivot.PivotTables
    If WSPivot.Cells(1, Tbl.TableRange1.Column) = "COMPLETE" Then
        Set TblAddress = WSPivot.Range(Tbl.TableRange1.Address)
        StTbl = WSPivot.Cells(TblAddress.Row + 1, TblAddress.Column).Address
        EndTbl = WSPivot.Cells(TblAddress.Rows.Count + 2, TblAddress.Column + TblAddress.Columns.Count - 1).Address
        Set TblAddress = WSPivot.Range(StTbl & ":" & EndTbl)
        '---> Sort the Table by #Days Reverse
        TblAddress.Sort key1:=WSPivot.Range(StTbl).Offset(0, 1), order1:=xlDescending, Header:=xlGuess
        '---> Remove Undesirables codes from the table
        Set TblAddress = Nothing
        For I = WSPivot.Range(StTbl).Row To WSPivot.Range(EndTbl).Row
            If WSPivot.Cells(I, WSPivot.Range(StTbl).Column) <> "0" And WSPivot.Cells(I, WSPivot.Range(StTbl).Column) <> "100-COMP" Then
                If TblAddress Is Nothing Then
                    Set TblAddress = WSPivot.Range(WSPivot.Cells(I, WSPivot.Range(StTbl).Column), WSPivot.Cells(I, WSPivot.Range(StTbl).Column + 1))
                    Set TblAddress = Union(TblAddress, WSPivot.Range(WSPivot.Cells(I, WSPivot.Range(StTbl).Column), WSPivot.Cells(I, WSPivot.Range(StTbl).Column + 1)))
                End If
            End If
        Next I
        Exit For
    End If

Next Tbl

'---> Get the Chart
'For I = 1 To WSChart.ChartObjects.Count
With WSChart.ChartObjects(ChrtIndex)
    LeftLabel = .Left + 3
    TopLabel = .Top + .Height + 15
    WidthLabel = .Width / 3 - 5
End With

'---> Get the Top6
For J = 1 To 6
    With WSChart.Shapes.AddTextbox(msoTextOrientationHorizontal, LeftLabel, TopLabel, WidthLabel, 100)
        '---> Set Font type and size
        .TextFrame.Characters.Font.Name = "Calibri"
        .TextFrame.Characters.Font.Bold = True
        .TextFrame.Characters.Font.Size = 8
        '---> Add Rectangle in the Bottom Right Corner with the Ranking
        With WSChart.Shapes.AddShape(msoShapeRectangle, LeftLabel + WidthLabel - 20, TopLabel + 80, 20, 20)
            .TextFrame.Characters.Text = J
            .TextFrame.HorizontalAlignment = xlHAlignCenter
            .Line.DashStyle = msoLineSolid
            .Line.Style = msoLineSingle
            .Line.ForeColor.RGB = RGB(0, 0, 0)
            .Name = "Cht " & Format(ChrtIndex) & " Rec " & Format(J)
            Group(J) = .Name
        End With
        '---> Loopukup Code Name, Avg days, %Open Days
        Code = TblAddress.Cells(J, 1)
        AvgDays = TblAddress.Cells(J, 2)
        Set cCell = WSPivot.Columns(TblAddress.Column).Find(what:="Grand Total", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            TotRow = cCell.Offset(0, 1)
            TotRow = TblAddress.Cells(TblAddress.Rows.Count, 3)
        End If
        PercOpen = TblAddress.Cells(J, 2) / cCell.Offset(0, 1)
        Set cCell = WSDesc.Range("A:A").Find(what:=Code, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            CodeDesc = WSDesc.Cells(cCell.Row, 2) & ", "
            CodeDesc = "Item not found, "
        End If
        '---> Add Text of the Header
        .TextFrame.Characters.Text = CodeDesc & AvgDays & " d" & Space(25)
        .TextFrame.Characters.Text = Left(.TextFrame.Characters.Text, 30) & Chr(9) & Format(PercOpen, " 0%") & Chr(10)
        '---> Find Col of the Code in the Table DataII
        Set cCell = WSData.Range("1:1").Find(what:="Reason Code 2", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
        If Not cCell Is Nothing Then
            ColCode = cCell.Column
            ColCode = 1
        End If
        '---> Make sure to remove filtering before sorting
        If WSData.AutoFilterMode = True Then WSData.ShowAllData
        '---> Sort the Table by  Ascending for that Code
        WSData.Range("A1").Sort key1:=WSData.Columns(ColCode), order1:=xlAscending, Header:=xlYes
        '---> Filter the Table for that Code
        WSData.UsedRange.AutoFilter Field:=ColCode, Criteria1:=Code
        '---> Record Length till now
        lenBold = Len(.TextFrame.Characters.Text)
        '---> Add All Stores
        For K = 2 To WSData.Range("A" & WSData.Rows.Count).End(xlUp).Row
            If WSData.Cells(K, 1).EntireRow.Hidden = False Then
                If .TextFrame.Characters.Text <> "" And Len(.TextFrame.Characters.Text) <> lenBold Then .TextFrame.Characters.Text = .TextFrame.Characters.Text & ", "
                .TextFrame.Characters.Text = .TextFrame.Characters.Text & WSData.Cells(K, 2)  '& "  -  " & WSData.Cells(K + 1, ColCode) & " days" & Chr(10)
            End If
        Next K
        '---> Set Top 3 as normal not bold
        .TextFrame.Characters(lenBold, Len(.TextFrame.Characters.Text) - lenBold).Font.Bold = False
        '---> Name the TextBox
        .Name = "Cht " & Format(ChrtIndex) & "Top " & Format(J)
        Group(J + 6) = .Name
        '---> Format the Box
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Line.ForeColor.RGB = RGB(0, 0, 0)
        '---> Set Position of the Next Box
        If J <> 3 Then
            LeftLabel = .Left + .Width + 3
        End If
        If J = 3 Then
            LeftLabel = WSChart.ChartObjects(ChrtIndex).Left + 3
            TopLabel = TopLabel + .Height + 3
        End If
    End With

Next J

'---> Group All the Shapes

WSChart.Shapes.Range(Array(Group(1), Group(2), Group(3), Group(4), Group(5), Group(6), Group(7), Group(8), Group(9), Group(10), Group(11), Group(12))).Group
'WSChart.Shapes.Range(Array("Top 1", "Rec 1", "Top 2", "Rec 2", "Top 3", "Rec 3", "Top 4", "Rec 4", "Top 5", "Rec 5", "Top 6", "Rec 6")).Group'
'WSChart.Shapes.Range(Array("Top 7", "Rec 7", "Top 8", "Rec 8", "Top 9", "Rec 9", "Top 10", "Rec 10", "Top 11", "Rec 11", "Top 12", "Rec 12")).Group
End Sub

Open in new window

Activate Macro CreateTop6Top3Labels

jmac001Author Commented:

Response to questions:

1.   For building the top 6 you would exclude reason codes 0 and 100-Comp. Reason code 0 means the store does not meet the critera of status = OPN.  100-Comp shows the store opened Complete and I only what to identify top 6 reason the store did not open complete.

2. If the reason code 2 field is blank and the status is not OPN then it is being excluded from the PIVOT and CHART2 and should also be exluded from being calculated in the Top 6

3. Yes if it necessary to sort the data to get the information to populate then it would be ColumnK-Reason Code 2

I will continue to test the workbook that you have attached and let you know if I have any problems.
jmac001Author Commented:
Hi Gowflow,

The only fix is related to the your questions regarding the 0 reasons code.  Once this is excluded from the being populated in the pivot and chart then it looks like every thing will populate correctly.
I do not understand your last post. Is there a problem with my solution ?
jmac001Author Commented:
The VBA changes the Complete Pivot table results to look at all records.  The criteria for the  table is Status = OPN only and not ALL.
You mean to say that the Table Complete should at all time be filtered by the Status OPN ? and then perform the rest of calculations?

Or you need at the end of the calculations to get back the filtering to be OPN for sheet Complete ?

Like I am not clear on where the problem is.
Are the charts lables come out correctly but you need OPN on complete ?
Chart Labels wrong because OPN is not on ?
I think after reading several times in between the lines .. I finally got what you meant to say without explicitly saying it and here it is:

You need the Table in the PIVOTS sheet to ALWAYS be filtered by OPN. My routine set the filters to all prior to starting which makes the total 99 instead of 56 when it is set to OPN. Although the stores comes out ok the total percentage comes out wrong.

Please check this version and I hope this is what you are looking for.

I please invite you to be more explicit and choose your words and read your post prior to posting as it would save ALL OF US a HELL OF A TIME trying to decrypt your wishes and intentions.

AS and Example read your question again putting yourself in our shoes like we know nothing of your business I will quote just 2 lines and you will see it is TOTAL CHINEESE with all due respect to chineese I know nothing of that language:

Tested Chart 2 and the Reason Code Name, # days, and % are all the same.  The labeling for the Top6 is the reason code name, #s, %. #s represents the number of stores.  

READ this sentence and I guarantee NO OONE on earth could understand what are reason code, #s, % #s .... without prior correct reference to these items explaining where they come from or what they are !!!!

Tks and glad we finally got to this one.
PLs post a link in here for any other question you may need help with. Presume you still have the issue of Chart3 which I hope the explanation will be smooth.

jmac001Author Commented:
Here is the link to Chart 3 Request. Hope that it is easy to understand the request.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 10
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now