# Thread: Give numbers to column starting from minimum to maximum (0 to....) Thanks:  1 Post #5260430 (1) Likes:  1 Post #5260430 (1)

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

Hi,

In the data column C I have got random numbers from 0 to 2211

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

For example...
Minimum number start in cell C63=0 so D63 will be replaced with 0
Next number is 3 in cell C65 so D64 will be replaced with 1
Next number is 7 in cell C10 so D10 will be replaced with 2
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.

Sample data...

ABCD
1
2
3
4
5Real numbersResult New Numbers
6221179
7221179
8221179
9221179
1072
11195160
12221179
13221179
14201871
1576925
16200769
17211374
18166536
19223
20214376
21169839
2229011
23194959
24181848
2579828
26208572
27187455
2880731
29184451
30908
31211173
32176343
33197862
34169638
351209
3675021
37198563
38211775
39193258
40183449
41200668
42198664
43177044
44183750
4572816
46201770
47167537
48199866
4935012
50191257
5176424
5292032
53174341
5492032
55165734
5646013
57186954
58165633
59284
60184753
6122510
62195961
6300
6473518
6531
66199065
6775522
68178545
6972514
70178946
7178826
72295
73166435
74166435
75175342
7672715
77200067
7879929
79220078
8074920
81181147
8280230
83306
84219577
8579727
8676323
8773017
8874319
89184552
90188756
91327
92172740
93211173

Sheet1

Using Excel 2000

Regards,
Kishan

editing...

3. ## Re: Give numbers to column starting from minimum to maximum (0 to....)

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)))
Ctrl+Shift+Enter

M.

4. ## Re: Give numbers to column starting from minimum to maximum (0 to....)

Originally Posted by 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)))
Ctrl+Shift+Enter

M.
Wow! Marcelo Branco, I liked the formula it is superb working as required

Thank you so much for your kind help

Have a nice weekend

Kind Regards,

Kishan

5. ## Re: Give numbers to column starting from minimum to maximum (0 to....)

Hi,

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

Would it be any VBA solution for this problem?

Using Excel 2000

Regards,

Kishan