Formula to arrange rows in numerical order.

MrsFrankieH

Active Member
Joined
Mar 25, 2011
Messages
323
Office Version
  1. 365
Platform
  1. Windows
318303442
4563141
2426364041
1719273337
19263036
1819252632
11173035
37

<colgroup><col span="5"></colgroup><tbody>
</tbody>

19263036
318303442
4563141
1117303537
1719273337
1819252632
2426364041

<colgroup><col span="5"></colgroup><tbody>
</tbody>

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
 
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!!
 
Last edited:
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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. :)

 
Last edited:
Upvote 0
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
 
Upvote 0
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!

 
Upvote 0
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!! :)
 
Upvote 0
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. :)
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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! :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top