Counting the number of entries by lotid

[welchs101]

I have a sheet see enclosed.

What i want is two cols for the output.
Lotid and Percentage

So, for a given lotid i want to count =>
(number of No Errors)/ (number_of_errors + number_of_No_Errors)

Notice that in the file there are multiple entries for a given lotid. But what i want is as i said above

For a single lotid (so need to generate a unique list of lotids) count
(number of No Errors)/ (number_of_errors + number_of_No_Errors)

[welchs101]

i used a technique from an earlier post that someone gave me and it works as well. see enclosed file.

One question i did have is how to get the unique list of lotids. What i did was use the advanced filter option to create a unique list. Is this the best most efficient way? This way is "manual".....is there a more "automatic method". If not thats ok.

[welchs101]

here is the table with the percentages

[jeffreybrown]

If you want unique values then...

The sum is not necessary with the Countifs in this scenario

[welchs101]

thanks!

Just curious how you came up with the unique list .........did you use the advanced filter option?

[jeffreybrown]

You're welcome and yes I used the advance filter. If you trun on the macro recorder and record those actions the recording gives some good code you can repeat over and over.

Here are a couple of options.

Sub CreateUniqueList()
    With Sheets("Sheet1")
        .Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=.Range("B1"), Unique:=True
    End With
End Sub

Sub FilterUnique()
    Dim Rng As Range, Dn As Range

    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then
                .Add Dn.Value, ""
            End If
        Next
        Range("B1").Resize(.Count).Value = Application.Transpose(.keys)
    End With
End Sub

[welchs101]

thanks for the info! very helpful.


On another issue related to this post:

Why did I not have to use the "SUM" on this one and I do have to use the "SUM" on the other one? I am not sure when i would need it and when i would not.

[jeffreybrown]

Instead of me searching for it, could you post the formula you are referring too. Thanks