Determine last occurrence of item in a table

  • 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

  • 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:


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


    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

    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:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!