Give numbers to column starting from minimum to maximum (0 to....)

[Kishan]

Hi,</SPAN></SPAN>

In the data column C I have got random numbers from 0 to 2211 </SPAN></SPAN>

I need to replace the random numbers of column C and want to give them number 0 to as long as data find in ascending order. Note: the duplicate numbers will be assignee the same number</SPAN></SPAN>

For example... </SPAN></SPAN>
Minimum number start in cell C63=0 so D63 will be replaced with 0</SPAN></SPAN>
Next number is 3 in cell C65 so D64 will be replaced with 1</SPAN></SPAN>
Next number is 7 in cell C10 so D10 will be replaced with 2</SPAN></SPAN>
Next number is 22 in cell C19 so D19 will be replaced with 3 so this way assigned all number 0 to 2211 with 0 to 79 in ascending order.</SPAN></SPAN>

Sample data...</SPAN></SPAN>

Book1
	A	B	C	D
1
2
3
4
5			Real numbers	Result New Numbers
6			2211	79
7			2211	79
8			2211	79
9			2211	79
10			7	2
11			1951	60
12			2211	79
13			2211	79
14			2018	71
15			769	25
16			2007	69
17			2113	74
18			1665	36
19			22	3
20			2143	76
21			1698	39
22			290	11
23			1949	59
24			1818	48
25			798	28
26			2085	72
27			1874	55
28			807	31
29			1844	51
30			90	8
31			2111	73
32			1763	43
33			1978	62
34			1696	38
35			120	9
36			750	21
37			1985	63
38			2117	75
39			1932	58
40			1834	49
41			2006	68
42			1986	64
43			1770	44
44			1837	50
45			728	16
46			2017	70
47			1675	37
48			1998	66
49			350	12
50			1912	57
51			764	24
52			920	32
53			1743	41
54			920	32
55			1657	34
56			460	13
57			1869	54
58			1656	33
59			28	4
60			1847	53
61			225	10
62			1959	61
63			0	0
64			735	18
65			3	1
66			1990	65
67			755	22
68			1785	45
69			725	14
70			1789	46
71			788	26
72			29	5
73			1664	35
74			1664	35
75			1753	42
76			727	15
77			2000	67
78			799	29
79			2200	78
80			749	20
81			1811	47
82			802	30
83			30	6
84			2195	77
85			797	27
86			763	23
87			730	17
88			743	19
89			1845	52
90			1887	56
91			32	7
92			1727	40
93			2111	73
Sheet1

Thank you in advance</SPAN></SPAN>

Using Excel 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[CyrusTheVirus]

editing...

 

[Marcelo Branco]

Try

Array formula in D6 copied down
=SUM(IF(FREQUENCY(C$6:C$93,C$6:C$93),IF(C$6:C$93 < C6,1)))<c6,1)))

Ctrl+Shift+Enter

M.</c6,1)))

 

[Kishan]

Try

Array formula in D6 copied down
=SUM(IF(FREQUENCY(C$6:C$93,C$6:C$93),IF(C$6:C$93 < C6,1)))<C6,1)))

Ctrl+Shift+Enter

M.</C6,1)))[ QUOTE]

Wow! Marcelo Branco, I liked the formula it is superb working as required </SPAN></SPAN>

Thank you so much for your kind help

Have a nice weekend</SPAN></SPAN>

Kind Regards,</SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

Hi,</SPAN>

The given example in the post#1 is with 93 rows and the formula provided in the post#3 works perfect with approximately very well with data rows 2500 but when today I wanted to use it for 58000 rows it just hang my computer twice a thrice may it is of my version and poor computer </SPAN></SPAN>

Would it be any VBA solution for this problem? </SPAN></SPAN>

Please help </SPAN></SPAN>

Thank you in advance</SPAN></SPAN>

Using Excel 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN>