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.

[TABLE="width: 931"]
<colgroup><col span="3"><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Transaction Date
dd/mm/yyyy[/TD]
[TD]Invoice Billing Date[/TD]
[TD]Financial Year[/TD]
[TD]Billing Period[/TD]
[TD]Formula Helper Column (Hide)[/TD]
[TD]Invoice #[/TD]
[TD]Revenue Type[/TD]
[TD]Customer Name[/TD]
[/TR]
[TR]
[TD]25/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC[/TD]
[TD]Feb-ABC-0006[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]5/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC[/TD]
[TD]Feb-ABC-0007[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]10/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC[/TD]
[TD]Feb-ABC-0008[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]10/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC[/TD]
[TD]Feb-ABC-0009[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]21/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-DEF[/TD]
[TD]Feb-DEF-0010[/TD]
[TD]Massage Therapy[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]20/03/2015[/TD]
[TD]31/03/2015[/TD]
[TD]2015[/TD]
[TD]3[/TD]
[TD]Mar-DEF[/TD]
[TD]Mar-DEF-0011[/TD]
[TD]Product Sales[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]

 

[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")
[TABLE="width: 803"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Transaction Date
dd/mm/yyyy[/TD]
[TD]Invoice Billing Date[/TD]
[TD]Financial Year[/TD]
[TD]Billing Period[/TD]
[TD]Invoice #[/TD]
[TD]Revenue Type[/TD]
[TD]Customer Name[/TD]
[/TR]
[TR]
[TD]25/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-Acajou-00001[/TD]
[TD]Product Sales[/TD]
[TD]Acajou[/TD]
[/TR]
[TR]
[TD]5/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC-00002[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]10/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC-00002[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]10/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-ABC-00002[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]21/02/2015[/TD]
[TD]28/02/2015[/TD]
[TD]2015[/TD]
[TD]2[/TD]
[TD]Feb-DEF-00003[/TD]
[TD]Massage Therapy[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]20/03/2015[/TD]
[TD]31/03/2015[/TD]
[TD]2015[/TD]
[TD]3[/TD]
[TD]Mar-DEF-00004[/TD]
[TD]Gift Voucher[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon3sy]

I knew I would need this solution in the future and this time I have used Gaz Chops.

Thank you again