Formula to arrange rows in numerical order.

M

MrsFrankieH

Active Member
Joined
Mar 25, 2011
Messages
323
Office Version
  1. 365
Platform
  1. Windows
[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
 
Sort by date Sort by votes
Re: Help with formula to arrange rows in numerical order.

May be something like this you are looking for, but i ain't suggest this when you work with large sets of data.


Excel 2013/2016
ABCDEFGHIJK
131830344219263036
24563141318303442
324263640414563141
417192733371117303537
5192630361719273337
618192526321819252632
711173035372426364041
Sheet7
Cell Formulas
RangeFormula
G1=SMALL($A$1:$A$7,ROWS($A$1:$A1))
H1=INDEX($B$1:$B$7,MATCH(G1,$A$1:$A$7,0))
I1=INDEX($C$1:$C$7,MATCH(G1,$A$1:$A$7,0))
J1=INDEX($D$1:$D$7,MATCH(G1,$A$1:$A$7,0))
K1=INDEX($E$1:$E$7,MATCH(G1,$A$1:$A$7,0))
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Re: Help with formula to arrange rows in numerical order.

Hello Peter!!! Absolutely. I'll post it as soon as I get back from the store. Thank you kind sir!! :)
 
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Hi Sam!! That's what I'm looking for!! Thank you so much. I'll try it and keep you updated!
 
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Hello Sam! I tried this formula and it works BRILLIANTLY for a small amount of data but like you mentioned, not a lot of data but it's exactly what I need. What do you suggest for large amounts of data?

Thank you so much!!!
 
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Hello Peter, I've just gotten back.
I see what you mean there.

[TABLE="width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 10.66px; margin-bottom: 0px;">[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]34[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]18[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=99CC00]#99CC00[/URL] , align: right"]34[/TD]
[TD="align: right"]37[/TD]
[/TR]
</tbody>[/TABLE]

3 18 26 30 36
3 18 30 34 37
3 18 30 34 42
17 19 27 33 37
18 19 25 26 32

Thank you Peter!! I have lots of data. Do you have a formula that can be dragged down to include a lot of data?? Thanks again, I really appreciate your help.
 
Upvote 0
Re: Help with formula to arrange rows in numerical order.

MrsFrankieH said:
Hello Sam! I tried this formula and it works BRILLIANTLY for a small amount of data ..
Click to expand...
Provided that data is not like my sample from post 9. ;)

I don't know what you mean by "a large amount of data" (columns and rows) or what version of Excel you are using.
If the suggestion below does not work adequately for you perhaps you can explain in what way it is deficient as well as clarifying the two issues I have mentioned above.

I would use some helper columns, G:H below & they could be hidden.
Formulas in G2 & H2 copied down. Note that G2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
Formula in J2 copied across and down.

I have also assumed all numbers are only 1 or 2 digits per the samples. Please clarify if that is not the case.

Excel Workbook
ABCDEFGHIJKLMN
1
2318303442103183034423318263036
31719273337117192733374318303437
4318263036103182630361318303442
518192526321181925263251719273337
63183034371031830343721819252632
Sheet1
 
Last edited:
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Actually, my formula for J2, across and down, should be much simpler, and the column H formula needs tweaking to protect against 2 (or more) rows being identical in all columns). :oops:

Excel Workbook
ABCDEFGHIJKLMN
1
2318303442103183034422318303437
31719273337117192733374318303442
4318303442103183034423318303442
518192526321181925263251719273337
63183034371031830343711819252632
Sheet1 (3)
 
Last edited:
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Hello Peter!! :)

What I meant by "large amounts of data" was rows and rows of 'draws' (the rows are 'draws'). Please excuse my lack of Excel vocabulary. :) I think once I learn how to express myself using the lingo, that would open up my Excel life. lol.
I'm using Office 13, so Excel 13 I believe. :)
I'm going to try this right now with your instructions and let you know how it comes out! Thank you kind Sir!
 
Upvote 0
Re: Help with formula to arrange rows in numerical order.

Peter thats cool:cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
304
Peter_SSs
Peter_SSs
A
  • Solved
Two-value combination extraction formula?
2
Replies
11
Views
207
bebo021999
B
S
  • Solved
Data manipulation with dynamic arrays
Replies
5
Views
78
Fluff
Fluff
S
  • Solved
Individual Ranking
2
Replies
11
Views
470
sksanjeev786
S
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

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