Results 1 to 3 of 3

Thread: Number data in a column based on its uniquness
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Number data in a column based on its uniquness

    Hello Everyone,
    I think my question / problem is simple but since i am still learning I need your help.

    I have 2 columns (ColA,ColB)
    ColA = Random volume of rows with some data which repeats frequently.
    ColB= should pick up a sequential number 1,2,3,4 for every new data in ColA.

    If a data repeats in ColA then ColB should repeat the same sequence number.

    I want to achieve this by putting some formula in ColB instead of Macros.
    Is this possible ? If someone can help me learn & find answers.

    Example : Example result of how ColB should appear / populate


    ColA ColB
    Donald 1
    Mickey 2
    Donald 1
    Mickey 2
    Jack 3
    Thomas 4
    Thomas 4
    Thomas 4
    Mickey 2

    Peter Piper 5


    So,every time data populates in ColA, ColB should pick up a sequential number. However the same number should repeat if the data repeats in ColA.
    "Donald" was the 1st data & it picked up 1 in ColB, now everytime Donald Repeats in ColA ColB= 1

    Also, if you notice when there is a New data in ColA = Peter Piper, ColB picked the next sequential number since its new data.

    Thanks in Advance for all the support & training.

    Regards,
    Steeve

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Number data in a column based on its uniquness

    Enter a 1 in B1 then the formula below in B2 and copy it down to your last data point in col A. Note this assumes you have no empty col A cells embedded in your name list.
    Sheet1

    AB
    1Donald1
    2Mickey2
    3Donald1
    4Mickey2
    5Jack3
    6Thomas4
    7Thomas4
    8Thomas4
    9Mickey2
    10Peteriper5

    Spreadsheet Formulas
    CellFormula
    B2=IF(A2="","",IF(COUNTIF(A$1:A2,A2)=1,MAX(B$1:B1)+1,INDEX(A$1:B1,MATCH(A2,A$1:A1,0),2)))


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Number data in a column based on its uniquness

    Thank you Very much JoeMo. This works perfect & gives exactly the solution. I just copied the formula across all rows I wanted this to calculate & it did perfect. Thank you very much again.. Appreciate It.

    Regards,
    Steeve



    Quote Originally Posted by JoeMo View Post
    Enter a 1 in B1 then the formula below in B2 and copy it down to your last data point in col A. Note this assumes you have no empty col A cells embedded in your name list.
    Sheet1





    A B
    1 Donald 1
    2 Mickey 2
    3 Donald 1
    4 Mickey 2
    5 Jack 3
    6 Thomas 4
    7 Thomas 4
    8 Thomas 4
    9 Mickey 2
    10 Peteriper 5

    Spreadsheet Formulas
    Cell Formula
    B2 =IF(A2="","",IF(COUNTIF(A$1:A2,A2)=1,MAX(B$1:B1)+1,INDEX(A$1:B1,MATCH(A2,A$1:A1,0),2)))


    Excel tables to the web >> Excel Jeanie HTML 4

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
  •