unique values with criteira.

[mmitch]

hi

i'm trying to calulate otif (on time in full) for deliveries we make.
B has the order number - i can calulate the number if unique orders (three)

I cannot work out how to calulate how many order were in full (c=3 and I = blank)
(c = 1 ordered, c=3 delivered, I = action: Backorder, Deleted or blank)
in this example, order 42211 was in full
42208 had a B (backorder) and D (deleted) in I so not in full
42210 bad a B (backorder) in I so not in full.

when i go live, there will be 6000 - 10000 lines a day, and i'll run the calulation for a week, so 30000 to 60000 line

A B C D E F G H I

12/06/2018	42208	1	10	5	13/06/2018		MM	B
12/06/2018	42208	3	10	5	13/06/2018	13/06/2018	MM
12/06/2018	42208	1	1	1	13/06/2018		MM
12/06/2018	42208	3	1	1	13/06/2018	13/06/2018	MM	D
12/06/2018	42210	1	1	1	13/06/2018		MM
12/06/2018	42210	3	1	0	13/06/2018		MM	B
12/06/2018	42210	1	1	1	13/06/2018		MM
12/06/2018	42210	3	1	1	13/06/2018	13/06/2018	MM
12/06/2018	42210	1	1	1	13/06/2018		MM
12/06/2018	42210	3	1	0	13/06/2018		MM
12/06/2018	42210	1	1	1	13/06/2018		MM
12/06/2018	42210	3	1	1	13/06/2018	13/06/2018	MM
12/06/2018	42211	1	1	1	13/06/2018		MM
12/06/2018	42211	3	1	1	13/06/2018	13/06/2018	MM

<colgroup><col style="mso-width-source:userset;mso-width-alt:2702;width:57pt" width="76"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3726;width:79pt" width="105"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2929;width:62pt" width="82"> <col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>

 

[LFKim2018]

try this on a separate sheet -- do not test directly on your work.

Crude but I think it worked.
hope this helps
thanks..

check this sample..https://www.dropbox.com/s/q060gkyyg3e9ozg/mmitch.xlsm?dl=0

Sub UpdateData()
    Sheets("Sheet1").Select
    mlr = Cells(Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    On Error Resume Next
    Dim i As Long
    For i = 2 To mlr
        If Cells(i, "I") = "" Then
            Range("XFD" & i) = 0
        Else
            Range("XFD" & i) = 1
        End If
        If Cells(i + 1, "B") <> Cells(i, "B") Then
            If Application.Sum(Range("XFD:XFD")) = 0 Then
                Cells(i, "J") = "FULL"
            Else
                Cells(i, "J") = "NOT FULL"
            End If
            Columns(16384).Clear
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    On Error GoTo 0
    Range("A2").Select
End Sub

 

[mmitch]

thanks. perfect

how does it work?

i understand the the basic code, but not the excel bits
(for next and if then I understand)

 

[LFKim2018]

the only code that I don't know is how to establish that there is a B or D along the column I - so I used a helper column (XFD - the farthest so as not to hamper any columns in between) - if the cell is empty, I assigned zero and one for the others - then I get the sum - if it is zero then there are no B nor D , meaning FULL.
hope that helped.
thanks..