Number and Renumber

[CharlesB1611]

Hello again,
I have a project that I am working on that requires a column (B) to be renumbered from 1 to x based on the reoccurring values in another column (C). The Columns and data look like this:

5​	2​	DATE 09/07/2022 WED
5​	5​	PRODUCT TOTAL
5​	7​	PLU#2820033062
5​	8​	2 X @ 9.94
5​	9​	P MRLBRO SM ICE T1 $19.88
5​	10​	PM MPD .50 2PK -0.01
5​	11​	PLU#60924990101
5​	12​	SN C SNUS ZYN 3 PEP T1 $3.99
5​	13​	SN ZYN 2.00 2PK -0.01
5​	14​	PLU#60924990701
5​	15​	SN C SNUS ZYN CIT 3 T1 $3.99
5​	16​	SN ZYN 2.00 2PK -0.01
5​	20​	PROMOTION TOTAL -0.03
5​	22​	TAX1 TAXABLE $27.83
5​	24​	TOTAL $27.83
5​	26​	SUBTOTAL $27.83
5​	27​	CASH $27.83
5​	28​	NO. 00000012 REG: POS-MAIN 13:42
5​	29​	Jon
5​	33​	DATE 09/07/2022 WED
5​	36​	PRODUCT TOTAL
5​	38​	PLU#840095781008
5​	39​	2021 HELIX INNOVATIO -0.50
5​	40​	PLU#840095781008
5​	41​	2021 HELIX INNOVATIO -0.50
5​	42​	PLU#28200197214
5​	43​	MARLBORO LFP 2020 -1.00
5​	44​	PLU#2820033062
5​	45​	2 X @ 9.94
5​	46​	P MRLBRO SM ICE T1 $19.88
5​	47​	PM MPD .50 2PK -0.50
5​	48​	PLU#7310000589
5​	49​	2 X @ 4.79
5​	50​	SN C SKOAL BL LCP T1 $9.58
5​	51​	MPD UST 1.00 SKL 2PK -1.00
5​	55​	PROMOTION TOTAL -1.50
5​	57​	TAX1 TAXABLE $27.96
5​	59​	TOTAL $25.96
5​	61​	SUBTOTAL $25.96
5​	62​	CASH $30.00
5​	63​	CHANGE $4.04
5​	64​	NO. 00000013 REG: POS-MAIN 14:11
5​	65​	Jon

What I would like to do now is renumber Column B so that every time the word DATE (the result from my last post) Column B will start sequentially numbering with the number 1 on the row where DATE is found and then start over with the next DATE. In this table sample the cell with the word Jon would be the last numbered value before the word DATE occurs. The word DATE could occur in other areas on column C but I only want to count the one at the beginning 4 characters.

 

[Fluff]

How about

=IF(LEFT(C2,4)="date",1,N(B1)+1)

 

[CharlesB1611]

Sorry, Looking for vba code

 

[Fluff]

Why not just use VBA to insert the formula?

 

[CharlesB1611]

Not sure how to do that so that it runs through all the rows.

 

[CharlesB1611]

Thank you for the formula! I had to add a single row at the beginning with a zero in B so that the numbers would be in the right place. It works but when I export it out I get a ,0, on the first line of the csv file. Not a big deal compared to everything else! Will deal with that another time. Thanks again!

 

[Fluff]

Glad you sorted it & thanks for the feedback.