Increment based on Adjecent Cells

[simonharry]

Hi All,

Not been here for a while. Hope you are all ok and can help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]A123
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A123
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A124
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A125
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have 2 columns as above. I want to add the incremental count of each number in column A into column B to look as below using VBA.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]A123
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A123
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A124
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A125
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A126
[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Thanks Simon

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Column A​

[/td][td="bgcolor:#F3F3F3"]

Column B​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]A123[/td][td]

1​

[/td][td]B2: =COUNTIF(A$1:A2, A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]A123[/td][td]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]A124[/td][td]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]A125[/td][td]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]A126[/td][td]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]A126[/td][td]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]A126[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]A126[/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]A126[/td][td]

5​

[/td][td][/td][/tr]
[/table]

 

[JugglerJAF]

Make sure that your data has a header row (so your value of A123 would be in cell A2).

In B2, type the following formula, then copy down:
=IF(A2<>A1,1,B1+1)

What it's doing is checking if the value in A2 is not equal to the value in A1. If that returns TRUE (because the values are not the same), then a value of 1 will be returned, otherwise (if the values are the same), then add 1 to the value immediately above in Column B.

This only works if your data is sorted by the values in Column A so that identical values are all adjacent.

 

[MARZIOTULLIO]

Hi

B1=COUNTIF($A$2:$A2,$A2) copy down

 

[simonharry]

As always, this forum delivers and then some. Thanks all. Much appreciated.