Formula to arrange rows in numerical order.

[MrsFrankieH]

[TABLE="width: 165"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]18[/TD]
[TD]30[/TD]
[TD]34[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]31[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]26[/TD]
[TD]36[/TD]
[TD]40[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]19[/TD]
[TD]27[/TD]
[TD]33[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]26[/TD]
[TD]30[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]19[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]17[/TD]
[TD]30[/TD]
[TD]35 [/TD]
[TD]

37​

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 165"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]26[/TD]
[TD]30[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18[/TD]
[TD]30[/TD]
[TD]34[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]31[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]17[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]19[/TD]
[TD]27[/TD]
[TD]33[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]19[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]26[/TD]
[TD]36[/TD]
[TD]40[/TD]
[TD]41[/TD]
[/TR]
</tbody>[/TABLE]

Hello Everyone!!

My computer is Dell and operating system is 10.

Can someone please help me with a formula to automatically arrange rows of numbers in numerical order? The top sets of numbers are how they look originally. The bottom sets are how I'd like them to look.

Thank you in advance.

Frankie x

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hello Peter!

Thank you so much! What I meant by "a large amount of data" is the number of rows. I have nearly 5000 rows of numbers to put in numerical order. Whew! lol.
I've put in the data per your instructions, no brackets, just CTR + Shift + Enter and for some reason, I'm getting a 'name error'.
I have Office 13 so I think it's Excel 13?
Is there any order that I should put in the formula? Maybe that's the problem? Looking forward to your answer. Thank you so much kind Sir.

I nearly forgot to add, yes, the numbers are 1 or 2 digit and there are 5 individual numbers in each row. I tried to copy and paste all of the numbers but there were too many and it crashed. Thanks again!!

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hello Peter!
Here's a screenshot of the error message I'm getting. This is after Ctrl + Shift + Enter.
Thanks again.

 

[Peter_SSs]

Re: Help with formula to arrange rows in numerical order.

I'm getting a 'name error'.

That is why I asked about your Excel version. Earlier version do not have the CONCAT function.
Since you only have 5 columns of data, try this formula in G2 instead. This is a standard formula not Control+Shift+Enter.

=(1&TEXT(A2,"00")&TEXT(B2,"00")&TEXT(C2,"00")&TEXT(D2,"00")&TEXT(E2,"00"))+0

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hello Peter!!! Thank you!! That newest formula worked like a charm but after I dragged it down, it produced errors after 6 rows. Is there a certain trick to having it drag down for indefinite rows?
Thanks again, I truly appreciate it!

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hello Peter! I think I figured it out. I'll keep you posted. Thanks again!!

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hi Peter! Ok, update. I thought if I extended the range of the red and blue 'reference lines' (I don't know what they're called), it would extend the range of the formula. That didn't work. It was a try.

 

[Peter_SSs]

Re: Help with formula to arrange rows in numerical order.

Hi Peter! Ok, update. I thought if I extended the range of the red and blue 'reference lines' ...

That sounds like you might still be using the J2 formula from post 17, not the updated one from post 18?

For a larger range the ..
G2 formula should be exactly as shown in post 23 and copied down to the last row of data in columns A:E.

H2 formula should change the blue 6 to whatever the last row of data is in columns A:E then copied down to the last row of data in columns A:E.
=RANK(G2,G$2:G$6,1)+COUNTIF(G$1:G1,G2)

J2 formula should change the blue 6s to whatever the last row of data is in columns A:E then copied across to column N and down to the last row of data in columns A:E.
=INDEX(A$2:A$6,MATCH(ROWS(J$2:J2),$H$2:$H$6,0))

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Hello Peter! I hope you had a wonderful Sunday!
The formula works. I did as you said, copied the formula and put in the appropriate cells and it really works (very cool by the way! )
The problem I'm facing is when I drag the cells. I'm dragging cells G2 to N2 down. When I do that, I still have errors. Are there particular cells I should drag down? For example, when I dragged down from G6, with the exception of a few "Value" errors, everything dragged down great. I tried the same with dragging down from H6, J6, K6, L6, M6., and N6 and I am still getting errors. I'm not sure why it's not working out. Would you like me to do a screenshot of the results? Maybe it's something I'm doing. Thanks again kind Sir.

 

[Peter_SSs]

Re: Help with formula to arrange rows in numerical order.

For example, when I dragged down from G6, with the exception of a few "Value" errors,

If you are getting #VALUE errors when dragging the G2 formula down, then you need to investigate those rows. It means that those rows are somehow different to the sample rows. Since I cannot see them I don't know what the issue is, but surely you need to eliminate whatever is causing those errors before you can successfully arrange your rows.

If you are providing any more sample data (eg with those errors) then please do so using a method that we can copy/paste into our sheets to test with. We are not able to copy/paste the data from an image like you have been posting. My signature block below has help on that.

 

[MrsFrankieH]

Re: Help with formula to arrange rows in numerical order.

Wow thank you Peter! I'm going to check out your screenshot help. Using photobucket is what my Dad used to call, a "shlepp". lol.
I appreciate your help more than i can express!! Thanks again!