Results 1 to 5 of 5

Thread: Increment based on Adjecent Cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,635
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  4. #4
    Board Regular
    Join Date
    Aug 2015
    Posts
    767
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Increment based on Adjecent Cells

    Hi

    B1=COUNTIF($A$2:$A2,$A2) copy down
    Last edited by MARZIOTULLIO; Jul 30th, 2018 at 01:36 PM.

  5. #5
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Increment based on Adjecent Cells

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •