+ Reply to Thread
Results 1 to 5 of 5

Incrementally Number Out of Order Values

  1. #1
    Registered User
    Join Date
    05-23-2015
    Location
    Chicago
    MS-Off Ver
    v15
    Posts
    2

    Incrementally Number Out of Order Values

    Sorry if this is somewhere on the forum already, I wasn't exactly sure what to search to find this answer.

    I'm not sure how to phrase this, so let me show you what I'm trying to achieve first:


    Column A Column B
    A 1
    A 2
    A 3
    B 1
    B 2
    C 1
    A 4
    D 1
    B 3
    A 5

    So basically, I need to incrementally number the various values in column A... into column B, but identical values won't always be be next to each other. It won't go A, A, A, B, C, D, D... it will be mixed up like the table above. Another important point is there's not a finite number of values for column A, I'm only using the alphabet to simplify my question.

    To start, I've figured out a way to count the number of count the total number of each value in Column A using =COUNTIF($A$2:$A,A2:A), leading to this result:

    Column A Column B
    A 5
    A 5
    A 5
    B 3
    B 3
    C 1
    A 5
    D 1
    B 3
    A 5

    I thought that might lead me somewhere, but now I'm stuck as to where to go from here. I did find this page showing how to do something similar:

    http://stackoverflow.com/questions/1...olumn-resettin

    =if(A2=A1,B1+1,1) seemed close... but that was for values that are in order.

    Anyone have any ideas on how I could achieve this? Is it possible with a formula, or do I need a Macro?

    Thanks for any help!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Incrementally Number Out of Order Values

    In B1,

    = COUNTIF(A$1:A1,A1)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Incrementally Number Out of Order Values

    =COUNTIF($A$1:A1,A1)
    Try this
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    05-23-2015
    Location
    Chicago
    MS-Off Ver
    v15
    Posts
    2

    Re: Incrementally Number Out of Order Values

    Wow! It was that simple?! Using shg's formula works great! Popipipo, looks like your idea was the same but with an added semicolon. Does that serve a purpose or was it a typo?

    Again, thanks so much guys!

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Incrementally Number Out of Order Values

    In the netherlands we use the semicolon.

    I corrected my typo

+ 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. [SOLVED] Return all values less than or equal to another number, but in order
    By LittleFry in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2014, 07:58 AM
  2. VBA to Rotate image incrementally based on cell values
    By SteveGilbert in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-06-2014, 12:15 PM
  3. [SOLVED] Copy Template Worksheet to Multiple Worksheets and Incrementally Number Sheets in Workbook
    By dingbat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-25-2014, 01:18 PM
  4. [SOLVED] Macro for Auto Inserting a Number Incrementally (+1) in the footer of Excel 2010
    By bobbyd98682 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2012, 03:38 PM
  5. How to increase values incrementally
    By Jediknights in forum Excel General
    Replies: 1
    Last Post: 04-21-2012, 12:44 AM

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