Determine last occurrence of item in a table

Kiwifinny · Jun 13th 2016

Hi
I have a customer transaction table with >700,000 records over a 10 week period by day, each day is identified and within each day there are multiple customers with some having a single line, others multiple lines, I wish to sum the volume based on the customer and the day without including the full 700,000 rows of data.
I think i first need to determine where the day's transactions end (Ordered by date), then within that set of data determine the number of rows of transactions for each customer. My brain just won't work and/or I just don't know how to accomplish this and would be grateful for any help.
Many thanks
Kiwifinny

Max1616 · Jun 13th 2016

Re: Determine last occurrence of item in a table

You can do something easy like this to create a column that sums up the kg for that specific customer on that specific day:

Macro:

Code

Sub PalletCalc()


Dim lrow As Long
Dim i As Long


lrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To lrow
    Cells(i, 9) = WorksheetFunction.SumIfs(Columns(7), Columns(3), Cells(i, 3), Columns(1), Cells(i, 1))
Next i
End Sub

Display More

This works too if you want a formula:
*place in I2, then drag down

=SUMIFS(G:G,A:A,A2,C:C,C2)

With either of these you can now look at that record to see that customers total Kgs for that given customer/day combination.The formula would be the fastest way to accomplish this without running a loop which could take a lot of time with 700k + rows.

Kiwifinny · Jun 14th 2016

Re: Determine last occurrence of item in a table

Many thanks, the formula works well.

**KjBox** · Jun 14th 2016

Re: Determine last occurrence of item in a table

This will give you the solution you need, albeit with each customer sub-total at the end of their sales for each date rather than the start.

Click the button on the attached workbook.

Code assigned to the button is:

Code

Sub btnGetTotals_Click()
    Dim x, e, Dates, Kgs, dKgs As Double, i As Long
    
    Application.ScreenUpdating = False
    With Sheets("sheet1")
        .[i2].Resize(.Cells(.Rows.Count, 9).End(xlUp).Row + 50).Clear
        x = .Cells(1).CurrentRegion
        With CreateObject("system.collections.arraylist")
            For i = 2 To UBound(x, 1)
                If Not .contains(x(i, 3)) Then .Add x(i, 3)
            Next
            Dates = .toarray
        End With
        With CreateObject("system.collections.arraylist")
            For Each e In Dates
                For i = 2 To UBound(x, 1) - 1
                    If x(i, 3) = e Then
                        If x(i, 1) = x(i + 1, 1) Then
                            dKgs = dKgs + x(i, 7)
                            .Add ""
                        Else
                            dKgs = dKgs + x(i, 7)
                            .Add dKgs
                            dKgs = 0
                        End If
                    End If
                Next
            Next
            If x(UBound(x, 1), 1) = x(UBound(x, 1) - 1, 1) And x(UBound(x, 1), 3) = x(UBound(x, 1) - 1, 3) Then
                dKgs = dKgs + x(UBound(x, 1), 7)
                .Add dKgs
            ElseIf x(UBound(x, 1), 1) = x(UBound(x, 1) - 1, 1) And x(UBound(x, 1), 3) <> x(UBound(x, 1) - 1, 3) Then
                .Reverse
                .Remove "": .Reverse
                .Add dKgs
                .Add x(UBound(x, 1), 7)
            Else
                .Add x(UBound(x, 1), 7)
            End If
            Kgs = .toarray
        End With
        .[i2].Resize(UBound(Kgs) + 1) = Application.Transpose(Kgs)
    End With
                
End Sub

Display More

Determine last occurrence of item in a table

Participate now!

Share