Results 1 to 5 of 5

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

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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





    Thank you in advance

    Using Excel 2000

    Regards,
    Kishan
    Last edited by Kishan; Apr 12th, 2019 at 07:22 PM.

  2. #2
    Board Regular CyrusTheVirus's Avatar
    Join Date
    Jan 2015
    Location
    Glens Falls, USA
    Posts
    732
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    editing...
    Last edited by CyrusTheVirus; Apr 12th, 2019 at 09:32 PM.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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.
    Last edited by Marcelo Branco; Apr 12th, 2019 at 11:31 PM.

  4. #4
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Marcelo Branco View Post
    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. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?


    Please help


    Thank you in advance


    Using Excel 2000


    Regards,

    Kishan


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
  •