Number data in a column based on its uniquness

[ashyn]

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

 

[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.

Excel Workbook
	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
Sheet1

 

[ashyn]

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

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
Donald
Mickey
Donald
Mickey
Jack
Thomas
Thomas
Thomas
Mickey
Peteriper

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:66px;"><col style="width:34px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]5[/TD]

</tbody>

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))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4