Need to increment untill another value is found

[Stiino0]

Hello all,

Just new to the site but have been lurking posts for a long time now, really helpful stuff.

Here's my problem (I want it in a formula/macro):

I want to increment a value in a certain column B for example untill infinity or untill the value of column A changes. And then the value should restart at 1 (value can be text all of a sudden ex. 11A, 15A, 15B,...)

so example:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I want to make it automated to that I can easily use a formula/macro to make +600 articles instantly correct.

Thanks in advance for any kind of help! If anymore info is needed, I'll be lurking my own post!

Kind regards,

Stiino0

 

[Stiino0]

Ok so in C2 paste this formula:

=IF(ROW(A1)=1,B2,COUNTIF($B$2:B2,B2))

I think I need to put ; instead of comma's. Or is this some kind of setting?

 

[Stiino0]

Ok so in C2 paste this formula:

=IF(ROW(A1)=1,B2,COUNTIF($B$2:B2,B2))

Sweet baby jesus it works!!

 

[Stiino0]

If you want to, can you explain how the formula works? Then I can implement it in other excel sheets as well. Wow love the support on this website

 

[sheetspread]

Sure, row(a1) is the counter that will increase by one with each row, so the formula knows to start with whatever term you have first, then count occurences within the cumulative range (notice the absence of dollar signs extends the range by one at each row increment). Since it's not counting below the current cell, you will have 1 for each new term, 2, etc until a new one appears.

 

[sheetspread]

I think I need to put ; instead of comma's. Or is this some kind of setting?

Yes in USA . is a decimal, other countries use a comma which their Excel formulas replace with a semicolon unless you change the setting.

 

[Stiino0]

If you want to, can you explain how the formula works? Then I can implement it in other excel sheets as well. Wow love the support on this website

Very good stuff, thanks! I love to formulas more than macroes but I think more can be done with macroes right?

 

[sheetspread]

I think more can be done with macroes right?

Yes, but if you just need something simple a formula is often enough.

 

[Stiino0]

Yes, but if you just need something simple a formula is often enough.

Thank you very much, until next time

 

[Stiino0]

Hi all,

The formula worked a year ago but after all the models that needed to be done were finished, I abandoned the formula... Now that I'm trying to revive it I can't seem to get it right again?! I always end up with a "new pagenumber" on my last page (where it should still continue to calculate the last page)

Unfortunately for me, the upload that I did a year ago isn't available anymore so I'll try to send a new one... Hope you guys are willing to do the same thing again...

Here's the file everything needs to come in column C for this example.

Thanks in advance! (again )

Kind regards,

Stiino0

 

[Stiino0]

Plz halp

Greatly appreciated and will be documented better... My bad :'(

Thanks in advance!

Kind regards,

Stiino0