Sequential numbering when X column repeats records

  • Thread starter Thread starter robpiolink
  • Start date Start date
R

robpiolink

I have excel file that has 2 rows

Row 1 has records
A
A
A
B
B
C
A
Row 2 need to have the number of times row one repeats a record.
Something like this
1
2
3
1
2
1

Any formula that will find records on column one and sequentially number
column B depending on amount of records?

Thank you

-RoMo
 
Im sorry. I meant to say columns not rows

I have excel file that has 2 Columns

Column 1 has records
A
A
A
B
B
C
A
Column 2 need to have the number of times row one repeats a record.
Something like this
1
2
3
1
2
1

Any formula that will find records on column one and sequentially number
column B depending on amount of records?
 
Assuming the data starts in cell A2...

Enter 1 in cell B2

Enter this formula in cell B3 and copy down as needed:

=IF(A3=A2,B2+1,1)
 
If the list begins in A2 then the formula in B2 is:

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

If the list is likely to be extended later use

=IF(ISBLANK(A2),"",COUNTIF($A$2:A2,A2)) and copy down as far as you like.

Peter Atherton
 
Back
Top