Sequential numbering of unique values in multiple columns

[Alexis0901]

I am hoping to find a formula to automatically number a large amount of data. I need to send individual invoices to each vendor and the transactions need to be grouped by date and listed on separate lines. I have a column with the invoice identifiers and a column with the transaction dates. I built an invoice in excel that uses sumif statements to pull the necessary data from another sheet but I need an invoice line identifier to know how many lines there will need to be on each invoice. Please see example of what I'm looking for below. The "Date count per invoice" is the column I'm hoping to automate.

Vendor	Invoice Number	Date	Date count per invoice
A0001	1	10/1/2019	1
A0001	1	10/1/2019	1
A0001	1	10/1/2019	1
A0001	1	10/5/2019	2
A0001	1	10/11/2019	3
A0001	1	10/19/2019	4
A0002	2	10/3/2019	1
A0002	2	10/3/2019	1
A0002	2	10/3/2019	1
A0002	2	10/15/2019	2
A0002	2	10/16/2019	3
A0003	3	9/1/2019	1
A0003	3	9/1/2019	1
A0003	3	9/15/2019	2
A0003	3	9/30/2019	3
A0003	3	10/3/2019	4
A0003	3	9/1/2019	1
A0003	3	10/10/2019	5
A0003	3	10/10/2019	5
A0003	3	10/20/2019	6
A0003	3	10/21/2019	7
A0004	4	10/17/2019	1
A0004	4	10/18/2019	2
A0004	4	10/19/2019	3
A0004	4	10/20/2019	4

<tbody>
</tbody>

Any help would be appreciated.

Thanks,
Alexis

 

[Eric W]

Welcome to the MrExcel forum.

Try:

Book1
	A	B	C	D	E
1	Vendor	Invoice Number	Date	Date count per invoice
2	A0001	1	10/1/2019	1
3	A0001	1	10/1/2019	1
4	A0001	1	10/1/2019	1
5	A0001	1	10/5/2019	2
6	A0001	1	10/11/2019	3
7	A0001	1	10/19/2019	4
8	A0002	2	10/3/2019	1
9	A0002	2	10/3/2019	1
10	A0002	2	10/3/2019	1
11	A0002	2	10/15/2019	2
12	A0002	2	10/16/2019	3
13	A0003	3	9/1/2019	1
14	A0003	3	9/1/2019	1
15	A0003	3	9/15/2019	2
16	A0003	3	9/30/2019	3
17	A0003	3	10/3/2019	4
18	A0003	3	9/1/2019	4	1
19	A0003	3	10/10/2019	5
20	A0003	3	10/10/2019	5
21	A0003	3	10/20/2019	6
22	A0003	3	10/21/2019	7
23	A0004	4	10/17/2019	1
24	A0004	4	10/18/2019	2
25	A0004	4	10/19/2019	3
26	A0004	4	10/20/2019	4
Sheet7

Cell Formulas
Range	Formula
D2	{=SUM(SIGN(FREQUENCY(IF($A$2:$A2=A2,IF($B$2:$B2=B2,$C$2:$C2)),$C$2:$C2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Note that row 18 varies from your example, but I believe the formula follows your request.

 

[DanteAmor]

Hi @Alexis0901, welcome to the forum!

Try this "regular" formula, It is a longer than the solution of Eric, but it fulfills all your requirement.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:94.1px;" /><col style="width:86.5px;" /><col style="width:92.2px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:37px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Vendor</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Invoice Number</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date count per invoice</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">01/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">01/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">01/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">05/oct/2019</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">11/oct/2019</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A0001</td><td style="text-align:right; ">1</td><td style="text-align:right; ">19/oct/2019</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >A0002</td><td style="text-align:right; ">2</td><td style="text-align:right; ">03/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >A0002</td><td style="text-align:right; ">2</td><td style="text-align:right; ">03/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >A0002</td><td style="text-align:right; ">2</td><td style="text-align:right; ">03/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >A0002</td><td style="text-align:right; ">2</td><td style="text-align:right; ">15/oct/2019</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >A0002</td><td style="text-align:right; ">2</td><td style="text-align:right; ">16/oct/2019</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">01/sep/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">01/sep/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">15/sep/2019</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">30/sep/2019</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">03/oct/2019</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">01/sep/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">10/oct/2019</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">10/oct/2019</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">20/oct/2019</td><td style="text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >A0003</td><td style="text-align:right; ">3</td><td style="text-align:right; ">21/oct/2019</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >A0004</td><td style="text-align:right; ">4</td><td style="text-align:right; ">17/oct/2019</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >A0004</td><td style="text-align:right; ">4</td><td style="text-align:right; ">18/oct/2019</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >A0004</td><td style="text-align:right; ">4</td><td style="text-align:right; ">19/oct/2019</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >A0004</td><td style="text-align:right; ">4</td><td style="text-align:right; ">20/oct/2019</td><td style="text-align:right; ">4</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IFERROR(IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)=1,SUMPRODUCT(MAX(($A1:A$2=A2)*($D1:D$2)))+1,SUMPRODUCT(MAX(($A1:A$2=A2)*($C1:C$2=C2)*($D1:D$2)))),1)</td></tr></table></td></tr></table>

 

[Eric W]

The main disadvantage to using an array formula is that you need to remember to press Control+Alt+Enter when entering it. And if you give the workbook to someone else, they may not be familiar with the concept. The advantage can be shorter formulas, and FREQUENCY formulas tend to be pretty efficient.

There are some functions which have array processing built in, such as SUMPRODUCT, AGGREGATE, or LOOKUP. As Dante correctly mentions, in this case you can write a function that does the same thing without needing to use Control+Shift+Enter. Here's a somewhat shorter SUMPRODUCT formula:

=SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2)/COUNTIFS(A$2:A2,A$2:A2,B$2:B2,B$2:B2,C$2:C2,C$2:C2))

Also, Dante, I don't see you using column B in your formula. If there are multiple invoices per vendor (which this example doesn't show, so maybe not an issue), I suspect your formula would give odd results.

 

[DanteAmor]

Also, Dante, I don't see you using column B in your formula. If there are multiple invoices per vendor (which this example doesn't show, so maybe not an issue), I suspect your formula would give odd results.

Thanks Eric for the comments. You are right, I did not consider column B, since it was not in the examples, but if there are multiple invoices per vendor, it is not a suspicion, my formula will surely have problems.

@Alexis0901,

I built an invoice in excel that uses sumif statements to pull the necessary data from another sheet but I need an invoice line identifier to know how many lines there will need to be on each invoice.

If you are going to fill out an invoice, perhaps the most practical is to create a macro for these purposes. The macro could automatically calculate the number of rows per invoice, among other things (Invoice counter, print, save as pdf, send by mail, save the file, generate all invoices, etc.)

 

[Peter_SSs]

Note that row 18 varies from your example,....

I also think there is an issue with row 18 but I'm wondering if it is the sample data as much as the expected result that is in error?

Since the OP said ..

..the transactions need to be grouped by date ...

.. I read it as that is what had happened (but also grouped by vendor & invoice number) & we are just trying to identify how many rows there are per vendor per invoice per date. Could be completely wrong but if that is the case then perhaps this (after I have altered C18 so that my assumption is correct. )

<b>Invoice</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:60px;" /><col style="width:108px;" /><col style="width:90px;" /><col style="width:155px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Vendor</td><td style="font-size:10pt; text-align:right; ">Invoice Number</td><td style="font-size:10pt; text-align:right; ">Date</td><td style="font-size:10pt; text-align:center; ">Date count per invoice</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">01-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">01-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">01-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">05-Oct-19</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">11-Oct-19</td><td style="font-size:10pt; text-align:center; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">A0001</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">19-Oct-19</td><td style="font-size:10pt; text-align:center; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">A0002</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">03-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">A0002</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">03-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">A0002</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">03-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">A0002</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">15-Oct-19</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">A0002</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">16-Oct-19</td><td style="font-size:10pt; text-align:center; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">01-Sep-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">01-Sep-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">15-Sep-19</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">30-Sep-19</td><td style="font-size:10pt; text-align:center; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">03-Oct-19</td><td style="font-size:10pt; text-align:center; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">03-Oct-19</td><td style="font-size:10pt; text-align:center; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">10-Oct-19</td><td style="font-size:10pt; text-align:center; ">5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">10-Oct-19</td><td style="font-size:10pt; text-align:center; ">5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">20-Oct-19</td><td style="font-size:10pt; text-align:center; ">6</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; ">A0003</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">21-Oct-19</td><td style="font-size:10pt; text-align:center; ">7</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; ">A0004</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">17-Oct-19</td><td style="font-size:10pt; text-align:center; ">1</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; ">A0004</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">18-Oct-19</td><td style="font-size:10pt; text-align:center; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; ">A0004</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">19-Oct-19</td><td style="font-size:10pt; text-align:center; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; ">A0004</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">20-Oct-19</td><td style="font-size:10pt; text-align:center; ">4</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(OR<span style=' color:008000; '>(A2<>A1,B2<>B1)</span>,1,N<span style=' color:008000; '>(D1)</span>+<span style=' color:008000; '>(C2<>C1)</span>)</td></tr></table></td></tr></table>

 

[Alexis0901]

These all work for what I need as long as I sort by vendor number and transaction date first.

Thank you so much for the help!
Alexis

 

[DanteAmor]

I'm glad you found a solution. Thanks for the feedback.

 

[Peter_SSs]

These all work for what I need as long as I sort by vendor number and transaction date first.

For my suggestion at least, that needs to be "as long as I sort by vendor number and invoice number and transaction date first"

Thank you so much for the help!
Alexis

You're welcome.