# Incrementing number formula solution

#### Jon3sy

##### Board Regular
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>

##### MrExcel MVP
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.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Jon3sy

##### Board Regular
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>

Replies
6
Views
404
Replies
4
Views
122
Replies
1
Views
143
Replies
0
Views
280
Replies
3
Views
292

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,493
Messages
5,832,015
Members
430,104
Latest member
briannnnleong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back