Complex IF to mark customer transactions Last transaction of day across ranges

Hi,

My first post on here, so please be gentle :-)

I have 3 columns of data, DATE, TIME and NAME of customer. Customers can have multiple transactions within the same day and different times and also across multiple days.

I currently have three text values as outcomes 1) No customer name at present 2) Not Last Transaction 3) Last Transaction. The Last Transaction acts as the final outcome for the customer within that day and time span.

I have a list of over 2000 customer transactions growing each day, mostly adding new customers each time, or repeating customers.

Currently I manually do the following :

1) Filter 'blank' customer name and manually add to all the cells in a column TRANSACTION STATUS - No customer name at present.

2) CUSTOMER NAME column I use duplicate conditional formatting, to highlight multiple transactions per customer.

3) SORT by CUSTOMER NAME, then DATE, then TIME.

4) FILTER, CUSTOMER NAME on NO FILL to show single transaction customers - TRANSACTION STATUS column - mark them as LAST TRANSACTION

5) FILTER, CUSTOMER NAME on DUPLICATE COLOUR, this will show all blank cells in TRANSACTION status column.

6) I then manually eyeball each customer name within the same day and different times. e.g. 4 x customer name, 3 transactions with single day with different times, and 1 transaction following day. For the 3 within the same day the earliest 2 transactions are marked as NOT LAST TRANSACTION, and the final transaction by time is marked as LAST TRANSACTION. the single Transaction the following day is also marked as LAST TRANSACTION.

7) I proceed down the list with each appended set of data.

8) To complicate matters manually some times the MISSING customer name is added at a later date, so I have to check for those. But a formula should remove this, as and when a name is added it would auto change.

There is an extra layer of complexity that I simply ignore at this time is that the transactions could be at a different location, hence single day with multiple times could be two sites, but that is too infrequent at present to worry about manually.

Any help would be great.

I don't know what TAGS to add.

I also have a follow up question on PIVOT tables in relation to this data, but will as per rules ask one thing at a time.

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Something like this:

=IF(A2="","",IF(AND(B2<>"",C2=""),"Missing",IF(COUNTIF(C$2:C2,C2)=COUNTIF(C:C,C2),"Last","Not Last")))