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)
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)
Last edited by hkbhansali; 04-15-2020 at 10:22 AM.
My English is very poor, so please be patient >_<"
Thanks & Regards.
hkbhansali
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!)
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?
Originally Posted by shg
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
Did you try putting the formula in A9 then copying down? As is, no edits?
yes but i want start from A9
ex. first small number start from A9
sorry it was my mistake
yes perfect..
Hi JBeaucaire
Thanks for help...
Hi MrShorty
Thanks for help....
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.
Not of the top of my head, sorry. Will think about it.
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.)?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks