Incrementing number formula solution

[Jon3sy]

The challenge is on! I have searched for other formulas for creating unique invoice auto-numbering but not satisfied with anything found thus far - I do not want to use VBA as the person managing the sheet has minimal excel skills and experience so want to keep it simple. Hopefully I have explained the following scenario well enough that it is understandable.

I tried creating a unique number to use as an invoice number using the "formula helper column". This may or may not be the right method so please anyone suggest a better alternative. I am using the assumption that a customer can make multiple purchases within a given month on different days but the formula needs to assign a unique same number for the multiple transactions within that month. Then on a separate sheet is the invoice template where the formula uses the concatenation of the Customer name + billing month to return the one invoice number for the multiple items regardless of what date each item was transacted on.

I started with this formula in the Invoice # column =IF(A2="","",E2&"-"&TEXT(COUNTIF(E$2:E13,E13),"0000")) but as you can see from the sample below every instance of Feb-ABC has a different invoice number when I want them to have the same number eg: in this sample the first 4 entries should have Feb-ABC-0001, the 5th entry should have Feb-DEF-0002, and the 6th entry Mar-ABC-0003 - or something like that to make it unique numbering. The transactional items may not be in a sequential order like those in the sample either ie: they could be anywhere within a range for a month.

Look forward to a solution.

Transaction Date dd/mm/yyyy	Invoice Billing Date	Financial Year	Billing Period	Formula Helper Column (Hide)	Invoice #	Revenue Type	Customer Name
25/02/2015	28/02/2015	2015	2	Feb-ABC	Feb-ABC-0006	Product Sales	ABC
5/02/2015	28/02/2015	2015	2	Feb-ABC	Feb-ABC-0007	Product Sales	ABC
10/02/2015	28/02/2015	2015	2	Feb-ABC	Feb-ABC-0008	Product Sales	ABC
10/02/2015	28/02/2015	2015	2	Feb-ABC	Feb-ABC-0009	Product Sales	ABC
21/02/2015	28/02/2015	2015	2	Feb-DEF	Feb-DEF-0010	Massage Therapy	DEF
20/03/2015	31/03/2015	2015	3	Mar-DEF	Mar-DEF-0011	Product Sales	DEF

<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Did you see my comment about Aladins formula? If the same Cust appears after a dif one it will adopt the last number! If that is what you want fine, but it doesn't look right to me.
...

Sort the data in ascending order on customer. If that's not possible, we can extend the formula or just go for including an additional range.

 

[Jon3sy]

Thanks gaz-chops - Yes I did see the comment but Aladin's second formula worked perfectly fine in my sheet and gave me the correct result - see below - cheers

=TEXT(A2,"mmm")&"-"&H2&"-"&
TEXT(SUM(IF(FREQUENCY(MATCH(CHOOSE({1},$H$2:H2&TEXT($A$2:A2,"mmm")),
CHOOSE({1},$H$2:H2&TEXT(A$2:A2,"mmm")),0),ROW($A$2:A2)-ROW($A$2)+1),1)),"000")

Transaction Date dd/mm/yyyy	Invoice Billing Date	Financial Year	Billing Period	Invoice #	Revenue Type	Customer Name
25/02/2015	28/02/2015	2015	2	Feb-Acajou-00001	Product Sales	Acajou
5/02/2015	28/02/2015	2015	2	Feb-ABC-00002	Product Sales	ABC
10/02/2015	28/02/2015	2015	2	Feb-ABC-00002	Product Sales	ABC
10/02/2015	28/02/2015	2015	2	Feb-ABC-00002	Product Sales	ABC
21/02/2015	28/02/2015	2015	2	Feb-DEF-00003	Massage Therapy	DEF
20/03/2015	31/03/2015	2015	3	Mar-DEF-00004	Gift Voucher	DEF

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>