count up based on number of records

[CLCoop]

I'm trying to auto input in sequential numbers in the COUNT (Column A) based on the invoice number (Column B) this can go go on for over a thousand records depending on how many invoice were put in the excel spreadsheet so it will vary. And is rather time stressed to do the counts manually. I'm looking for either VBA or formula to help do it for me Right now I am looking at each number in Column B then putting in a number starting at 1 and going on up to 799 (was a recent one, about cried).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Count (Column A)[/TD]
[TD]InvoiceNumber (Column B)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21554479[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21554479[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21559296[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21559296[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21566988[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21566988[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22466113[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22466113[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22466113[/TD]
[/TR]
</tbody>[/TABLE]

I hope this was more challenging not me just not able to think it through after 10 hours straight programming!

 

[RileyC]

You could make an array (or file for a large count of invoices) that holds a class/ text that contains both the invoice number and the the count of that invoice. Whenever a unique invoice is found add it to the file matching with the closest invoice less than the invoice number. Can you use vba?

 

[jtakw]

Hi,

If the invoice numbers in Column B is sorted/grouped as you've shown in your sample:

Book1
	A	B
1	Count (Column A)	InvoiceNumber (Column B)
2	1	21554479
3	1	21554479
4	2	21559296
5	2	21559296
6	3	21566988
7	3	21566988
8	4	22466113
9	4	22466113
10	4	22466113
Sheet246

Cell Formulas
Range	Formula
A2	=IF(B2<>B1,MAX(A$1:A1)+1,A1)

A2 formula copied down.

 

[CLCoop]

Hi,

If the invoice numbers in Column B is sorted/grouped as you've shown in your sample:

	A	B
Count (Column A)	InvoiceNumber (Column B)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21554479[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]21554479[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]21559296[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]21559296[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21566988[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21566988[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22466113[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22466113[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22466113[/TD]

</tbody>

Sheet246​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=IF(B2<>B1,MAX(A$1:A1)+1,A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

A2 formula copied down.

THANKS this worked great, I cheated and recorded a macro to auto input the formula so it is working great thank you for your help!

 

[jtakw]

You're welcome.