+ Reply to Thread
Results 1 to 11 of 11

Arrange random number in ascending format

  1. #1
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Arrange random number in ascending format

    Hi,

    I have a Random number from 1 to 100 in Range D3:M11,
    how can i arrange in ascending order in column A1:A100.

    Thanks

    (Test File Attached)
    Attached Files Attached Files
    Last edited by hkbhansali; 04-15-2020 at 10:22 AM.
    My English is very poor, so please be patient >_<"

    Thanks & Regards.
    hkbhansali

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Arrange random number in ascending format

    With no spaces in between:
    A1: =IFERROR(SMALL($D$3:$M$11, ROW(A1)), "")
    ...copy down.


    With spaces in between the way you showed in your workbook:
    A1: =IF(COUNTIF($D$3:$M$11, ROW(A1))>0, ROW(A1), "")
    ...copy down.
    Last edited by JBeaucaire; 04-15-2020 at 11:09 AM. Reason: Second formula posted was the first again, oops.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Arrange random number in ascending format

    How representative is your sample file? In this particular file, I could:

    1) use a COUNTIFS() function to determine if a given value/row number exists in the random range. COUNTIFS($D$3:$M$11,ROW()) would return 0 if the row number does not exist, or a 1 (or higher) if it does exist.
    2) Then a simple IF() function to test if the number exists and return empty string if it does not and return the row number if it does. =IF(COUNTIFS(...)>=1,ROW(),"")

    Does something like that work for you?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Arrange random number in ascending format

    Hi

    This formula is perfect for my use
    A1: =IFERROR(SMALL($D$3:$M$11, ROW(A1)), "")

    if i want it copy from cell A9 instead of A1 , how?

    Please help

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Arrange random number in ascending format

    Did you try putting the formula in A9 then copying down? As is, no edits?

  6. #6
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Arrange random number in ascending format

    yes but i want start from A9
    ex. first small number start from A9

    sorry it was my mistake

    yes perfect..

  7. #7
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Arrange random number in ascending format

    Hi JBeaucaire

    Thanks for help...

  8. #8
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Arrange random number in ascending format

    Hi MrShorty

    Thanks for help....

  9. #9
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: Arrange random number in ascending format

    Hi JBeaucaire

    Sorry I have reopen this thread.
    I want some modification in formula if possible.

    A1: =IFERROR(SMALL($D$3:$M$11, ROW(A1)), "") this formula arrange number in ascending order

    but is it possible that number display first come first basis..

    Thanks in advance.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Arrange random number in ascending format

    Not of the top of my head, sorry. Will think about it.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Arrange random number in ascending format

    What do you mean by "first come first basis"? Are you wanting to start in the upper left and work through the table top to bottom then left to right (41, 61, 82, 13, etc.)? Or do you want to start in the upper left and work through the table left to right then top to bottom (5, 13, 19, 16, etc.)? Do you want column A sparsely populated as in the original example (41 in A5, 61 in A7, 82 in A18, etc.)?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to sort numbers from a random number generator in ascending order?
    By mhoey8 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2019, 06:58 AM
  2. How can I arrange numbers in ascending order but....
    By mikehende in forum Excel General
    Replies: 2
    Last Post: 11-15-2016, 03:18 PM
  3. Arrange cells in ascending order
    By pankaj221 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-28-2016, 04:42 PM
  4. Arrange data in ascending order
    By bezbid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2016, 10:22 AM
  5. [SOLVED] Re-Arrange Data Ascending
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-11-2015, 08:52 AM
  6. Formulas to auto arrange in ascending
    By cyee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 07:45 AM
  7. How to arrange in ascending order all subtotals
    By faisal123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 06:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1