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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### petertenthije

##### Active Member
You could add a new helper column and place this formula. then combine the results of both helper columns into one column.

=if(countifs(\$E\$1:E1;\$E2)>0;vlookup(\$E2;\$E\$1:F1;2;false);max(\$F\$1:F1)+1)

edited to add dollar signs to lock the first row.

Last edited:

#### Jon3sy

##### Board Regular
Thank you Peter but that formula didn't work even when I tried some variations of it and referenced row 2 as opposed to row 1 - it didn't like the first argument of \$E\$1:E1 can you please review and revise? and when you say combine the results do I assume you mean concatenate the results?

Cheers

#### petertenthije

##### Active Member
It is working on my computer? You say the first \$E\$1:E1 is not working. Are you perhaps using COUNTIF instead of COUNTIFS?With combining the results I did mean concatenate them.

##### MrExcel MVP
 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Transaction Date Invoice Billing Date Financial Year Billing Period Invoice # Revenue Type Customer 2​ 25-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 3​ 5-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 4​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 5​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 6​ 21-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-DEF-002 Massage Therapy DEF 7​ 20-Mar-15​ 31-Mar-15​ 2015​ 3​ Mar-DEF-001 Product Sales DEF

The helper column is not used in the set up that follows...

 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Transaction Date Invoice Billing Date Financial Year Billing Period Invoice # Revenue Type Customer 2​ 25-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 3​ 5-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 4​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 5​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 6​ 21-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-DEF-002 Massage Therapy DEF 7​ 20-Mar-15​ 31-Mar-15​ 2015​ 3​ Mar-DEF-001 Product Sales DEF

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=TEXT(A2,"mmm")&"-"&H2&"-"&
TEXT(SUM(IF(FREQUENCY(IF(\$A\$2:A2-DAY(\$A\$2:A2)+1=DATE(YEAR(A2),MONTH(A2),1),
MATCH(\$H\$2:H2,\$H\$2:H2,0)),ROW(\$A\$2:A2)-ROW(\$A\$2)+1),1)),"000")
``````

#### gaz_chops

##### Well-known Member
@Aladin, that returns 001 for Mar, should be 003.

##### MrExcel MVP
[sic]

The helper column is not used in the set up that follows...

 Row\Col A​ B​ C​ D​ E​ F​ G​ H​ 1​ Transaction Date Invoice Billing Date Financial Year Billing Period Invoice # Revenue Type Customer 2​ 25-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 3​ 5-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 4​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 5​ 10-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-ABC-001 Product Sales ABC 6​ 21-Feb-15​ 28-Feb-15​ 2015​ 2​ Feb-DEF-002 Massage Therapy DEF 7​ 20-Mar-15​ 31-Mar-15​ 2015​ 3​ Mar-DEF-001 Product Sales DEF

<tbody>
</tbody>

F2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````
=TEXT(A2,"mmm")&"-"&H2&"-"&
TEXT(SUM(IF(FREQUENCY(IF(\$A\$2:A2-DAY(\$A\$2:A2)+1=DATE(YEAR(A2),MONTH(A2),1),
MATCH(\$H\$2:H2,\$H\$2:H2,0)),ROW(\$A\$2:A2)-ROW(\$A\$2)+1),1)),"000")
``````

@Aladin, that returns 001 for Mar, should be 003.

Right... It should be:

F2, control+shift+enter, not just enter, and copy down...
Rich (BB code):
``````
=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")
``````

#### gaz_chops

##### Well-known Member
@ Aladin, still doesn't appear to work, if you change the last Customer to ABC it returns 002 when it should be 001.
OP says "The transactional items may not be in a sequential order like those in the sample".

My formula is similar to Peter's and requires a helper column(2)

=IFERROR(INDEX(\$F\$1:F1,MATCH(\$E2,\$E\$1:E1,0)),MAX(\$F\$1:F1)+1)

Code:
``````[TABLE="width: 745"]
<tbody>[TR]
[TD]dd/mm/yyyy[/TD]
[TD]Invoice Billing Date[/TD]
[TD]Financial Year[/TD]
[TD]Billing Period[/TD]
[TD]Helper1[/TD]
[TD]Helper2[/TD]
[TD]Invoice #[/TD]
[TD]Revenue Type[/TD]
[TD]Customer Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]05/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]10/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]10/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]21/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-DEF[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-DEF-0002[/TD]
[TD]Massage Therapy[/TD]
[TD]DEF[/TD]
[TD]Feb-DEF-002[/TD]
[/TR]
[TR]
[TD="align: right"]20/02/15[/TD]
[TD="align: right"]31/03/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-002[/TD]
[/TR]
</tbody>[/TABLE]``````

#### Jon3sy

##### Board Regular
Wow awesome super fast responses! Thank you all for your suggestions - Aladin I chose your solution as it removed the need to use the helper columns so thank you so much and I am sure this will become a solution for others to use as an option for any unique auto-numbering functions whether it be invoice numbering, document numbering, product/item and much more.

I am forever indebted to this forum and all you wonderful gurus.

#### gaz_chops

##### Well-known Member
Wow awesome super fast responses! Thank you all for your suggestions - Aladin I chose your solution as it removed the need to use the helper columns so thank you so much and I am sure this will become a solution for others to use as an option for any unique auto-numbering functions whether it be invoice numbering, document numbering, product/item and much more.

I am forever indebted to this forum and all you wonderful gurus.

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.

Code:
``````[TABLE="width: 746"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]25/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]05/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]10/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]10/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-001[/TD]
[/TR]
[TR]
[TD="align: right"]21/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-DEF[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-DEF-0002[/TD]
[TD]Massage Therapy[/TD]
[TD]DEF[/TD]
[TD]Feb-DEF-002[/TD]
[/TR]
[TR]
[TD="align: right"]20/02/15[/TD]
[TD="align: right"]31/03/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-002[/TD]
[/TR]
[TR]
[TD="align: right"]25/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-002[/TD]
[/TR]
[TR]
[TD="align: right"]05/02/15[/TD]
[TD="align: right"]28/02/15[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Feb-ABC[/TD]
[TD="align: right"]1[/TD]
[TD]Feb-ABC-0001[/TD]
[TD]Product Sales[/TD]
[TD]ABC[/TD]
[TD]Feb-ABC-002[/TD]
[/TR]
</tbody>[/TABLE]``````

Replies
13
Views
459
Replies
12
Views
310
Replies
4
Views
92
Replies
1
Views
312
Replies
6
Views
355

1,196,027
Messages
6,012,954
Members
441,740
Latest member
abaz21

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