Create a number sequence for each change in a column in excel [Quick Tip]

Posted on June 29th, 2009 in Excel Howtos , Learn Excel - 10 comments

Create sequence numbers based on change in a column valuesHere is a quick formula trick you can use to generate sequence numbers that only increment when there is a change.

Assuming the sequence of values are in column C from C3,

  • you can write the following formula in B4 onwards (B3 will be 1, wake up…)
    =IF(C4=C3,B3,B3+1)
  • Now just copy paste the formula over the entire range B2:Bxxx to generate sequence numbers.

How it works?

  • That is for your home work for the day 😛

Browse more excel quick tips and learn something cool.

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Create a number sequence for each change in a column in excel [Quick Tip]”

  1. Peter H says:

    I have used a similar technique using conditional formatting to identify when lists change. Our Production Scheduling Program gives a readout like this:-
    Dept M/c Tool Job No
    A A1 T1 J1
    A A1 T1 J2
    A A1 T2 J3
    A A2 T2 J4
    A A2 T3 J5
    A A2 T3 J6
    A A2 T4 J7
    A A2 T5 J8
    A A2 T6 J9
    B B1 T7 J10
    B B1 T7 J11
    B B1 T7 J12
    B B1 T8 J13
    B B2 T9 J14
    B B2 T10 J15
    B B2 T10 J16
    B B2 T10 J17
    B B2 T10 J18

    If you add 2 conditional formats to each cell:-
    =A2=A1 - font colour equals white &
    =A2A1 - add a line to the top of the cell it comes out like this:-

    Dept M/c Tool Job No
    A A1 T1 J1
    J2
    T2 J3
    J4
    A2 T3 J5
    J6
    T4 J7
    T5 J8
    T6 J9
    B B1 T7 J10
    J11
    J12
    T8 J13
    B2 T9 J14
    T10 J15
    J16
    J17
    J18
    With the proper spread sheet with the horizontal lines drawn this is much easier to read

  2. Peter H says:

    Hi Chandoo

    Sorry about the formatting of the above post.
    I am new at this commenting game & dont know how to make the formatting come out correct

  3. Chandoo says:

    @Peter H: Welcome to PHD and commenting. 🙂 I like your idea. When I get sometime I will create a screenshot of this and post it here so that other readers will know how it would look.

    Also, you dont need 2 conditional formats. You can set the cell font color to white for all in one stroke and then use conditional formatting to turn the color to black when a2 not equal to a1.

  4. ronnie lack says:

    dilbert hello, my name is roni have 62 anos.vamos interesa what I'm learning to move in excel, as do combinations of 1.2.3 up to 25, using 15 numbers

  5. Cammy says:

    Hi,
    I'm trying to create a formula that generate sequence number for employees who have multiple lines. Example...
    ID Name Seq. #
    11 Smith, John 1
    11 Smith, John 2
    55 Perez, A

  6. Cammy says:

    ID Name Seq. #
    65 Matthews, Joe 1
    65 Matthews, Joe 2
    65 Matthews, Joe 3
    65 Matthews, Joe 4

  7. Maharaskal says:

    Hi guys,
    I need help on sequence number for sheets.
    could anyone guide me the correct formula for that?

  8. Chris says:

    I have a massive list of Data this needs to have sequence be numbers in sequence in the colum beside the data is raandom in cells, PLease see the example underneath it will be easier to explain

    Data237828
    Data237828
    Data237828
    Data237828
    Data237828
    Data237828
    Data237828
    Data237828
    Data876378
    Data876378
    Data876378
    Data876378
    Data876378
    Data876378
    Data876378
    Data7868
    Data7868
    Data7868
    Data893798389
    Data893798389
    Data893798389
    Data893798389
    Data893798389
    Data893798389
    Data234352
    Data234352
    Data234352
    Data234352

    Anyone know how to write a sequence formula so the final product in the column beside will have a sequence numbering please see below

    Data237828 1
    Data237828 2
    Data237828 3
    Data237828 4
    Data237828 5
    Data237828 6
    Data237828 7
    Data237828 8
    Data876378 1
    Data876378 2
    Data876378 3
    Data876378 4
    Data876378 5
    Data876378 6
    Data876378 7
    Data7868 1
    Data7868 2
    Data7868 3
    Data893798389 1
    Data893798389 2
    Data893798389 3
    Data893798389 4
    Data893798389 5
    Data893798389 6
    Data234352 1
    Data234352 2
    Data234352 3
    Data234352 4

    Any help is appreciated, thank you.

  9. Charan says:

    Hi Chris,

    I had a similar requirement and I have achieved this using a simple formula...
    =IF(A1A2,1,B1+1)

    and apply this formula for all rows in B column... hope this solves your issue.

    • Vijay says:

      Thanks Charan!! I was looking for the similar for formula. Even though very simple, in crunch time, nothing will work. Thanks Charan for great help

Leave a Reply