Thanks:  0
Likes:  0

# Thread: Number data in a column based on its uniquness

1. ## 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. ## 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

 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

 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

3. ## 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

Originally Posted by JoeMo
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

 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