Create Invoice Number based on 2 columns and a counter

[DarrMik]

Shared sheet through SharePoint.

I have created an invoice tracking table that has about 40 columns. The formula I am currently using to create the invoice number concatenates the TRUCK#, DATE, and the addition of a counter.  I have been manually adjusting this counter whenever the truck and date combination were not unique. 

* Note: Each trip has a Load # associated with it that I have no control over *

I am looking for a formula that will auto increment the counter portion of my invoice number AND is not volatile (does not change when columns are sorted.)  The COUNTIF function (not familiar with it) was suggested to me for creating the counter, but it seems to be volatile as this piece of the invoice number ALWAYS showed in order (1, 2, 3, ...)

This screen cap is an example of the desired results when sorting on the Load # column.

* Table may be sorted on any column

Any suggestions??

DarrMik

[#2]

You say the Load # you have NO control over BUT: a) is it persistent (i.e. will it stay the same over time or by your comment do you mean others may update/change it) and b) is it unique?
Next, do you have Excel 365 or some older version of Excel (i.e. we need to know which functions are available)
basically I am thinking of a formula like (i.e. I just wrote it here without testing it because it still depends on your answers):
=[@Truck]&"-"&Text([@Date],"YYMMDD")&"-"&Match([@[Load #]],Sort(Filter([Load #],([Truck]&[Date])=([@Truck]&[@Date])))

[#3]

@mtarler 

a) is it persistent

- Yes...the Load # for that trip (truck/date combination) does not change once set

b) is it unique?

- No....The Load # is set by multiple different vendors, so theoretically Vendor A may have the same Load # as Vendor B.

The truck/date/load # combination is unique though.  The boss wants the "counter" instead of the load # added to the end of the invoice as the load # can be any where from 3 (smallest so far) to 11 (length is unlimited) characters (alpha/numeric) long.

[#4]

ok so did you try my formula?

[#5]

I just tested it.....looks like it is stable (resorted on different columns and it maintained.) Far closer than I got.

When I add an additional trip (load id) with the same truck/date combination, the "counter" from the first load that was entered is incremented and the new load is appended with a '1.' This is close, but I need the new load to be appended with the '2' or '3' (what ever the current count is) and the load that is already in the list to maintain their "counter."

[#6]

It will 'lock in' after all the loads for that day are finished but yeah if new loads are added with different load names it will likely change the numbers.
The only suggestion I have for you is to add a entry time. Then you can do that same filter/sort/match but use entry time and then it is guaranteed to be in order of when you enter it.

[#7]

actually another solution would be to add a macro that creates the tracking number on entry but that wont work if you use excel online or if the settings are set to block macros. I would recommend adding a column called entry time and if you click CTRL-SHIFT-; (CTRL-:) that will automatically insert the time. the replace the [Load #] in the formula with the name of the new Time Stamp column.

[#8 · Accepted solution]

@mtarler 

The time column makes it work exactly as I am looking for....assuming the user remembers to enter the current time into the sheet. Hmmmm....just couldn't rely on that.  So...

I found a method that will enter the current time into the sheet automatically using the NOW() function and circular reference.   Source

Formula in TIME column:

=IF(A2<>"",IF(B2<>"",B2,NOW()),"")

Now, using this formula to set the time in a hidden/locked cell, I can use your formula (adjusted) to get the exact outcome I am looking for.

Final formula using the new (auto populating) TIME column:

=IF([@[Truck '#]]<>"", [@[Truck '#]]&"-"&TEXT([@[SERVICE
DATE]],"YYMMDD")&"-"&MATCH([@TIME],SORT(FILTER([TIME],([Truck '#]&[SERVICE
DATE])=([@[Truck '#]]&[@[SERVICE
DATE]])))),"")

As an added bonus, these formulas also work on the web version, so the user can enter the data from either the web browser or from within the Excel app.

Thx @mtarler for all your help