Incrementing number formula solution

Jon3sy

Board Regular
Joined
Jan 30, 2013
Messages
100
Office Version
  1. 365
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 DateFinancial YearBilling PeriodFormula Helper Column (Hide)Invoice #Revenue TypeCustomer Name
25/02/201528/02/201520152Feb-ABCFeb-ABC-0006Product SalesABC
5/02/201528/02/201520152Feb-ABCFeb-ABC-0007Product SalesABC
10/02/201528/02/201520152Feb-ABCFeb-ABC-0008Product SalesABC
10/02/201528/02/201520152Feb-ABCFeb-ABC-0009Product SalesABC
21/02/201528/02/201520152Feb-DEFFeb-DEF-0010Massage TherapyDEF
20/03/201531/03/201520153Mar-DEFMar-DEF-0011Product SalesDEF

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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
Transaction DateInvoice Billing DateFinancial YearBilling PeriodInvoice #Revenue TypeCustomer
2​
25-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
3​
5-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
4​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
5​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
6​
21-Feb-15​
28-Feb-15​
2015​
2​
Feb-DEF-002Massage TherapyDEF
7​
20-Mar-15​
31-Mar-15​
2015​
3​
Mar-DEF-001Product SalesDEF

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

Row\Col
A​
B​
C​
D​
E​
F​
G​
H​
1​
Transaction DateInvoice Billing DateFinancial YearBilling PeriodInvoice #Revenue TypeCustomer
2​
25-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
3​
5-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
4​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
5​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
6​
21-Feb-15​
28-Feb-15​
2015​
2​
Feb-DEF-002Massage TherapyDEF
7​
20-Mar-15​
31-Mar-15​
2015​
3​
Mar-DEF-001Product SalesDEF

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")
 
Upvote 0
[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 DateInvoice Billing DateFinancial YearBilling PeriodInvoice #Revenue TypeCustomer
2​
25-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
3​
5-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
4​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
5​
10-Feb-15​
28-Feb-15​
2015​
2​
Feb-ABC-001Product SalesABC
6​
21-Feb-15​
28-Feb-15​
2015​
2​
Feb-DEF-002Massage TherapyDEF
7​
20-Mar-15​
31-Mar-15​
2015​
3​
Mar-DEF-001Product SalesDEF

<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")
 
Upvote 0
@ 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]
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top