Formula to arrange rows in numerical order.

[MrsFrankieH]

3	18	30	34	42
4	5	6	31	41
24	26	36	40	41
17	19	27	33	37
1	9	26	30	36
18	19	25	26	32
11	17	30	35	37​

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

1	9	26	30	36
3	18	30	34	42
4	5	6	31	41
11	17	30	35	37
17	19	27	33	37
18	19	25	26	32
24	26	36	40	41

<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

 

[Sam_D_Ben]

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
	A	B	C	D	E	F	G	H	I	J	K
1	3	18	30	34	42		1	9	26	30	36
2	4	5	6	31	41		3	18	30	34	42
3	24	26	36	40	41		4	5	6	31	41
4	17	19	27	33	37		11	17	30	35	37
5	1	9	26	30	36		17	19	27	33	37
6	18	19	25	26	32		18	19	25	26	32
7	11	17	30	35	37		24	26	36	40	41
Sheet7

Cell Formulas
Range	Formula
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))

 

[Sam_D_Ben]

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

Please ignore-

Reason - while copy pasting i missed some of the rows and columns. So in-short i texted "Please ignore" Thank You

 

[MrsFrankieH]

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!!

 

[MrsFrankieH]

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!

 

[MrsFrankieH]

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!!!

 

[MrsFrankieH]

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

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

	A	B	C	D	E
1	3	18	30	34	42
2	17	19	27	33	37
3	3	18	26	30	36
4	18	19	25	26	32
5	3	18	30	34	37

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 10.66px; margin-bottom: 0px;">
</tbody>

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.

 

[Peter_SSs]

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 ..

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	3	18	30	34	42		10318303442	3		3	18	26	30	36
3	17	19	27	33	37		11719273337	4		3	18	30	34	37
4	3	18	26	30	36		10318263036	1		3	18	30	34	42
5	18	19	25	26	32		11819252632	5		17	19	27	33	37
6	3	18	30	34	37		10318303437	2		18	19	25	26	32
Sheet1

 

[Peter_SSs]

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).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	3	18	30	34	42		10318303442	2		3	18	30	34	37
3	17	19	27	33	37		11719273337	4		3	18	30	34	42
4	3	18	30	34	42		10318303442	3		3	18	30	34	42
5	18	19	25	26	32		11819252632	5		17	19	27	33	37
6	3	18	30	34	37		10318303437	1		18	19	25	26	32
Sheet1 (3)

 

[MrsFrankieH]

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!

 

[Sam_D_Ben]

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

Peter thats cool