1. Increment based on Adjecent Cells

Hi All,

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

 Column A Column B A123 A123 A124 A125 A126 A126 A126 A126 A126

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.

 Column A Column B A123 1 A123 2 A124 1 A125 1 A126 1 A126 2 A126 3 A126 4 A126 5

Thanks Simon

2. Re: Increment based on Adjecent Cells

 A B C 1 Column A Column B 2 A123 1 B2: =COUNTIF(A\$1:A2, A2) 3 A123 2 4 A124 1 5 A125 1 6 A126 1 7 A126 2 8 A126 3 9 A126 4 10 A126 5

3. Re: Increment based on Adjecent Cells

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.

4. Re: Increment based on Adjecent Cells

Hi

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

5. Re: Increment based on Adjecent Cells

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