Increment based on Adjecent Cells

simonharry

New Member
Joined
Jun 17, 2016
Messages
7
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

<tbody>
</tbody>

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

<tbody>
</tbody>


Thanks Simon
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,678
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​
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
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.
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top