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

 

[dispelthemyth]

Is Column A and Column B your heading or does your data start in row 1?

If you have headers try the below


B2 will always become 1 and it will scroll down as far as row 1000 and only stop if the value in column A of the row its looking at is blank

Sub IncrementColumnB()

Dim R as integer

Cells(2, 2).Value = 1
For R = 3 To 1000
If Cells(R, 1).Value = "" Then
Exit Sub
End If


If Cells(R, 1).Value = Cells(R - 1, 1).Value Then
Cells(R, 2).Value = Cells(R - 1, 2).Value + 1
Else
Cells(R, 2).Value = 1
End If




Next R


End Sub

 

[sheetspread]

=if(row(a1)=1,a1,countif($a$1:a1,a1))

 

[Stiino0]

Thanks I'll give this a go! I'll have to make a new macro for this then?!

 

[Stiino0]

Is Column A and Column B your heading or does your data start in row 1?

If you have headers try the below


B2 will always become 1 and it will scroll down as far as row 1000 and only stop if the value in column A of the row its looking at is blank

Sub IncrementColumnB()

Dim R as integer

Cells(2, 2).Value = 1
For R = 3 To 1000
If Cells(R, 1).Value = "" Then
Exit Sub
End If


If Cells(R, 1).Value = Cells(R - 1, 1).Value Then
Cells(R, 2).Value = Cells(R - 1, 2).Value + 1
Else
Cells(R, 2).Value = 1
End If




Next R


End Sub

Well actually Column B needs to increment when A is always 1 1 1 1 but needs to restart from 1 if Column A is 2 2 2 2

So I don't think this will work? or am I misreading? I'll give a bigger example:

[TABLE="width: 57"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

See? B keeps incrementing untill the value of A changes.

thanks for the quick responses!

 

[Stiino0]

=if(row(a1)=1,a1,countif($a$1:a1,a1))

Thanks for the formula, but I think it won't increment after 1 serie? as in I won't be able to make 600 articles done at once?

 

[dispelthemyth]

Why would you need to make a new macro, is your data not in the same format as your original post?

if its different, provide a sample of it changing anything that is not allowed to be shared


if its the same you can just paste this into "This Workbook" if you want to do it on multiple sheets or onto the sheet name if you want it just for a specific sheet

If you need advice on how to do this let me know

 

[sheetspread]

Thanks for the formula, but I think it won't increment after 1 serie? as in I won't be able to make 600 articles done at once?

I tried it on extended data with text as well as numbers, why don't you think 600 groups will work?

 

[Stiino0]

Why would you need to make a new macro, is your data not in the same format as your original post?

if its different, provide a sample of it changing anything that is not allowed to be shared


if its the same you can just paste this into "This Workbook" if you want to do it on multiple sheets or onto the sheet name if you want it just for a specific sheet

If you need advice on how to do this let me know

I'll upload a complete file. The data can be shared. Column B is the one that changes after a while. Column D is the one that needs to increment!

http://rapidshare.com/share/A6F0F1E10EBCF5AAC3BFE7E501A5BC7C

 

[sheetspread]

I'll upload a complete file. The data can be shared. Column B is the one that changes after a while. Column D is the one that needs to increment!

http://rapidshare.com/share/A6F0F1E10EBCF5AAC3BFE7E501A5BC7C

Ok so in C2 paste this formula:

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