count up based on number of records

CLCoop

Board Regular
Joined
May 30, 2018
Messages
56
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).
Count (Column A)InvoiceNumber (Column B)
121554479
121554479
221559296
221559296
321566988
321566988
422466113
422466113
422466113

<tbody>
</tbody>

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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
Hi,

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


Book1
AB
1Count (Column A)InvoiceNumber (Column B)
2121554479
3121554479
4221559296
5221559296
6321566988
7321566988
8422466113
9422466113
10422466113
Sheet246
Cell Formulas
RangeFormula
A2=IF(B2<>B1,MAX(A$1:A1)+1,A1)


A2 formula copied down.
 
Upvote 0
Hi,

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

AB
1Count (Column A)InvoiceNumber (Column B)
2121554479
3121554479
4221559296
5221559296
6321566988
7321566988
8422466113
9422466113
10422466113

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet246

Worksheet Formulas
CellFormula
A2=IF(B2<>B1,MAX(A$1:A1)+1,A1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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!
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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