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
Determine last occurrence of item in a table


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
Display MoreSub 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
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.

Re: Determine last occurrence of item in a table
Many thanks, the formula works well.

Re: Determine last occurrence of item in a table
This will give you the solution you need, albeit with each customer subtotal 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
Display MoreSub 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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!