Incrementing number formula solution

Jon3sy

Board Regular
Joined
Jan 30, 2013
Messages
95
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>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Joined
Jan 30, 2013
Messages
95
Office Version
  1. 365
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 DateFinancial YearBilling PeriodInvoice #Revenue TypeCustomer Name
25/02/201528/02/201520152Feb-Acajou-00001Product SalesAcajou
5/02/201528/02/201520152Feb-ABC-00002Product SalesABC
10/02/201528/02/201520152Feb-ABC-00002Product SalesABC
10/02/201528/02/201520152Feb-ABC-00002Product SalesABC
21/02/201528/02/201520152Feb-DEF-00003Massage TherapyDEF
20/03/201531/03/201520153Mar-DEF-00004Gift VoucherDEF

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top