+ Reply to Thread
Results 1 to 3 of 3

how to increment column B based on column A value?

  1. #1
    Registered User
    Join Date
    04-05-2020
    Location
    PA
    MS-Off Ver
    O365
    Posts
    5

    Question how to increment column B based on column A value?

    Hi -
    I have column A with values that repeat and then change ever few rows.
    I would like to create a formula for column B to increment once for each occurrence of the A value.

    Starting with
    'A'
    10
    10
    20
    20
    20

    I would like to create these values in column B
    'A' 'B'
    10 1
    10 2
    20 1
    20 2
    20 3

    So finally (I could do this separately) to create column 'C' as a concatenation of 'A' and 'B'
    'A' 'B' 'C'
    10 1 101
    10 2 102
    20 1 201
    20 2 202
    20 3 203

    Thank you in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: how to increment column B based on column A value?

    at B1
    =COUNTIF(A$1:A1,A1)
    at C1
    =A1& B1
    or direct
    A1&COUNTIF(A$1:A1,A1)

  3. #3
    Registered User
    Join Date
    04-05-2020
    Location
    PA
    MS-Off Ver
    O365
    Posts
    5

    Re: how to increment column B based on column A value?

    This worked perfectly. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-05-2018, 06:04 AM
  2. [SOLVED] Formula required to Auto Increment number based on letter in another column
    By deneh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2015, 05:36 PM
  3. Increment based on column A
    By rreddy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2014, 06:05 AM
  4. [SOLVED] Increment a Number in cells of same column based on how many of a specified data is found
    By BeachRock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 03:13 PM
  5. [SOLVED] Restart and increment numbers in column based on changed value in adjacent column
    By Kespin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2013, 06:26 PM
  6. [SOLVED] Increment a number based on the text in the adjacent column
    By lday75 in forum Excel General
    Replies: 7
    Last Post: 07-27-2012, 03:42 PM
  7. [SOLVED] increment a column based on information in another column
    By dhemlinger in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-25-2005, 04:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1