Creating a monthly defect pareto

[Arthur Ramaglie]

I have a spreadsheet of defect descriptions that include a part number, the day the defect was found, and the number of defects that occurred for that observation, among other information. What I want to do is search the column with part numbers until I find a certain part number, obtain the defect count for that instance, and continue on until the end of the data. Then I want to calculate the number of defects that occurred for that part number per month, and create a table on another sheet. There can be multiples of the same date for a part number, and there are also dates missing, but the dates are listed in ascending order. I am a beginner VBA programmer. This is what I have so far.
The part numbers are in column C, the dates are in column A, and the counts are in column F

Sub Macro8()
Dim Count As Integer, TotalCount As Integer, EnterAssembly As String
    Sheets("Defects").Select
    TotalCount = 0
    EnterAssembly = "xxxxxxx"
    i = 2
    Do Until ActiveCell.Value = vbNullString
        Range("Ci").Select
        If ActiveCell.Value = EnterAssembly Then
            Count = ActiveCell.Offset(0, 3)
            TotalCount = TotalCount + Count
        Else
            i = i + 1
        End If
    Loop
End Sub

I have also tried this, but this doesn't work either.

Sub GetDefects()
Dim EnterAssembly As String, EnterDate As Date, dtDate1 As Date, i As Integer, j as integer, MyRange as Range

'Obain values for assembly and start date
    EnterDate = InputBox("Enter the date that the pareto will start from.")
    EnterAssembly = InputBox("Enter the assembly number.")
'Calculate # of defects per month
    Windows("Running daily Inspection Breakdown.xlsx").Activate
    j = 0
    cCount = 0
    Do Until ActiveCell.Value = vbNullString
        i = 2
        Do Until ActiveCell.Value = vbNullString
            Set MyRange = Range("Ai").Select
            dDate1 = MyRange.Value
            If dDate1 <= WorksheetFunction.EoMonth(EnterDate, j) Then
                cCount = cCount + MyRange.Offset(0, 5)
                i = i + 1
            Else
                Windows("Pareto Macro.xlsm").Activate
                Range("$B$(j+4)").Select
                cCount = Range("$B$(j+4)").Value
                Exit Do
            End If
        Loop
        j = j + 1
    Loop
End Sub

Any advice would be much appreciated.

Moved to VBA Macros

 

[Hui]

Arthur

Firstly, Welcome to the Chandoo.org Forums

Have you thought about simply using a Pivot table to do what you want ?

Could you upload a sample file to assist us to assist you ?

 

[Arthur Ramaglie]

Thank you. Im not too familiar with Pivot tables, but I did try using one. But I was getting bizarre results because there are multiple places where the date, part number, and count are all the same, but they represent different defects, which wasn't being represented with the pivot table. Attached is a piece of the datasheet that shows how it is formatted. However the real file is over 30,000 rows. Thank you again.

 

[Hui]

Try the following
On sheet2 select you data include headings
Insert, Pivot Table
New Worksheet
In the Pivot Table Fields Dialog
Drag Date to Rows
Drag Assembley Number to Column
Drag Area of Defect to Column
Drag Count to Sum Values
Right Click on the Dates, Group
Select months, Ok

This should give you 99% of what your after
See attached

 

[Arthur Ramaglie]

ok it works on the example that you sent me, but when I apply it to the entire datasheet, it is only counting the rows. i.e. if for one date, the count number is 7, it is only counting that single occurrence as 1 in the pivot table.

 

[Arthur Ramaglie]

Nevermind I got it. it was counting the values, not summarizing them. Thank you!

 

[Hui]

You can sort by a field, Right click the field and select Sort
You can add a Pivot Chart also