Arrange numbers small to big in ascending order

[motilulla]

Using Excel 2010

Hello,

I have number in columns D:H I want a macro which can make them in ascending order from n1 through n5 small to bigger number or A to Z

Note: I want a macro which can be used in Excel 2000 that has only 3 fileds to make them in order. But here are the 5 fileds.

Here is the descending order data example.

Excel Question ExcelFourms.xls
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				5	6	7	8	9
7				4	6	7	8	10
8				4	5	7	9	10
9				3	6	7	9	10
10				3	5	8	9	10
11				2	6	8	9	10
12				1	7	8	9	10
13				4	5	7	8	11
14				3	6	7	8	11
15				4	5	6	9	11
16				3	5	7	9	11
17				2	6	7	9	11
18				3	4	8	9	11
19				2	5	8	9	11
20				1	6	8	9	11
21				3	5	6	10	11
22				3	4	7	10	11
23				2	5	7	10	11
24				1	6	7	10	11
25				2	4	8	10	11
26				1	5	8	10	11
27				2	3	9	10	11
28				1	4	9	10	11
29				4	5	6	8	12
30				3	5	7	8	12
31				2	6	7	8	12
32				3	5	6	9	12
33				3	4	7	9	12
34				2	5	7	9	12
35				1	6	7	9	12
36				2	4	8	9	12
37				1	5	8	9	12
38				3	4	6	10	12
39				2	5	6	10	12
40				2	4	7	10	12
41				1	5	7	10	12
42				2	3	8	10	12
43				1	4	8	10	12
44				1	3	9	10	12
45				3	4	5	11	12
46				2	4	6	11	12
47				1	5	6	11	12
48				2	3	7	11	12
49				1	4	7	11	12
50				1	3	8	11	12
51				1	2	9	11	12
52				4	5	6	7	13
53				3	5	6	8	13
54				3	4	7	8	13
55				2	5	7	8	13
56				1	6	7	8	13
57				3	4	6	9	13
58				2	5	6	9	13
59				2	4	7	9	13
60				1	5	7	9	13
61				2	3	8	9	13
62				1	4	8	9	13
63				3	4	5	10	13
64				2	4	6	10	13
65				1	5	6	10	13
66				2	3	7	10	13
67				1	4	7	10	13
68				1	3	8	10	13
69				1	2	9	10	13
70				2	4	5	11	13
71				2	3	6	11	13
72				1	4	6	11	13
73				1	3	7	11	13
74				1	2	8	11	13
75				2	3	5	12	13
76				1	4	5	12	13
77				1	3	6	12	13
78				1	2	7	12	13
79				3	5	6	7	14
80				3	4	6	8	14
81				2	5	6	8	14
82				2	4	7	8	14
83				1	5	7	8	14
84				3	4	5	9	14
85				2	4	6	9	14
86				1	5	6	9	14
87				2	3	7	9	14
88				1	4	7	9	14
89				1	3	8	9	14
90				2	4	5	10	14
91				2	3	6	10	14
92				1	4	6	10	14
93				1	3	7	10	14
94				1	2	8	10	14
95				2	3	5	11	14
96				1	4	5	11	14
97				1	3	6	11	14
98				1	2	7	11	14
99				2	3	4	12	14
100				1	3	5	12	14
101				1	2	6	12	14
102				1	3	4	13	14
103				1	2	5	13	14
104				3	4	6	7	15
105				2	5	6	7	15
106				3	4	5	8	15
107				2	4	6	8	15
108				1	5	6	8	15
109				2	3	7	8	15
110				1	4	7	8	15
111				2	4	5	9	15
112				2	3	6	9	15
113				1	4	6	9	15
114				1	3	7	9	15
115				1	2	8	9	15
116				2	3	5	10	15
117				1	4	5	10	15
118				1	3	6	10	15
119				1	2	7	10	15
120				2	3	4	11	15
121				1	3	5	11	15
122				1	2	6	11	15
123				1	3	4	12	15
124				1	2	5	12	15
125				1	2	4	13	15
126				1	2	3	14	15
127				3	4	5	7	16
128				2	4	6	7	16
129				1	5	6	7	16
130				2	4	5	8	16
131				2	3	6	8	16
132				1	4	6	8	16
133				1	3	7	8	16
134				2	3	5	9	16
135				1	4	5	9	16
136				1	3	6	9	16
137				1	2	7	9	16
138				2	3	4	10	16
139				1	3	5	10	16
140				1	2	6	10	16
141				1	3	4	11	16
142				1	2	5	11	16
143				1	2	4	12	16
144				1	2	3	13	16
145				3	4	5	6	17
146				2	4	5	7	17
147				2	3	6	7	17
148				1	4	6	7	17
149				2	3	5	8	17
150				1	4	5	8	17
151				1	3	6	8	17
152				1	2	7	8	17
153				2	3	4	9	17
154				1	3	5	9	17
155				1	2	6	9	17
156				1	3	4	10	17
157				1	2	5	10	17
158				1	2	4	11	17
159				1	2	3	12	17
160				2	4	5	6	18
161				2	3	5	7	18
162				1	4	5	7	18
163				1	3	6	7	18
164				2	3	4	8	18
165				1	3	5	8	18
166				1	2	6	8	18
167				1	3	4	9	18
168				1	2	5	9	18
169				1	2	4	10	18
170				1	2	3	11	18
171				2	3	5	6	19
172				1	4	5	6	19
173				2	3	4	7	19
174				1	3	5	7	19
175				1	2	6	7	19
176				1	3	4	8	19
177				1	2	5	8	19
178				1	2	4	9	19
179				1	2	3	10	19
180				2	3	4	6	20
181				1	3	5	6	20
182				1	3	4	7	20
183				1	2	5	7	20
184				1	2	4	8	20
185				1	2	3	9	20
186				2	3	4	5	21
187				1	3	4	6	21
188				1	2	5	6	21
189				1	2	4	7	21
190				1	2	3	8	21
191				1	3	4	5	22
192				1	2	4	6	22
193				1	2	3	7	22
194				1	2	4	5	23
195				1	2	3	6	23
196				1	2	3	5	24
197				1	2	3	4	25
198
199
200
201
202
203
204
Sheet4

Thank you.


Regards,
Moti

 

[DanteAmor]

Hi @motilulla. I hope you're well.

I want a macro which can be used in Excel 2000 that has only 3 fileds to make them in order. But here are the 5 fileds.

In the following macro change xlAscending to xlDescending if you want them from highest to lowest.

Change the 5 to 3 if you need 3 columns.

In the macro I am highlighting the data that you can change.

Sub Arrange_Numbers()
  Dim c As Range
  Application.ScreenUpdating = False
  For Each c In Range("D6", Range("D" & Rows.Count).End(3))
    c.Resize(1, 5).Sort c, xlAscending, Header:=xlNo, Orientation:=xlSortRows
  Next
  Application.ScreenUpdating = True
End Sub

--------------​

Let me know the result and I'll get back to you as soon as I can.​

Cordially​

Dante Amor​

--------------​

 

[motilulla]

Hi @motilulla. I hope you're well.



In the following macro change xlAscending to xlDescending if you want them from highest to lowest.

Change the 5 to 3 if you need 3 columns.

In the macro I am highlighting the data that you can change.

Sub Arrange_Numbers()
  Dim c As Range
  Application.ScreenUpdating = False
  For Each c In Range("D6", Range("D" & Rows.Count).End(3))
    c.Resize(1, 5).Sort c, xlAscending, Header:=xlNo, Orientation:=xlSortRows
  Next
  Application.ScreenUpdating = True
End Sub

--------------​

Let me know the result and I'll get back to you as soon as I can.​

Cordially​

Dante Amor​

--------------​

Hello @Dante Amor. Yes thank you all is fine. Hope you are also find healthy.

I as usually insert module and placed macro I run it several times but seems does not happen any action, please can you check or advise what I am missing.

Kind Regards,
Moti

 

[Peter_SSs]

@motilulla
Could you give a smaller sample (10 rows should be sufficient) & include the expected results?

 

[DanteAmor]

I as usually insert module and placed macro I run it several times but seems does not happen any action, please can you check or advise what I am missing.

The macro is very fast.

Change xlAscending to xlDescending so you can see the changes


Then change them again, you will see how the numbers change.

 

[DanteAmor]

In your example, the data is already ordered from smallest to largest. So the macro sorts them from smallest to largest, that's what you asked for.
So apparently no change.
That's why I ask you please to change the order in the macro to xlDescending

Then change them again to xlAscending

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[motilulla]

The macro is very fast.

Change xlAscending to xlDescending so you can see the changes


Then change them again, you will see how the numbers change.

Ok. Dante Amor. Yes true changing that as you say I can see the effect but I realize it is my mistake I want to sort as show below here is an example with 10 rows as @Peter_SSs asked. For example in cells D6:H15 there are original data I want to sort them as shown in the Cells D21:H30 first n1 then n2 then n3 then n4 then n5. I mean Column wise

Excel Question ExcelFourms.xls
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				5	6	7	8	9
7				1	7	8	9	10
8				2	5	8	9	11
9				2	5	7	10	11
10				1	5	8	9	12
11				3	4	5	11	12
12				3	5	6	7	14
13				1	5	7	8	14
14				1	3	8	9	14
15				2	3	4	9	17
16
17
18
19
20				n1	n2	n3	n4	n5
21				1	3	8	9	14
22				1	5	7	8	14
23				1	5	8	9	12
24				1	7	8	9	10
25				2	3	4	9	17
26				2	5	7	10	11
27				2	5	8	9	11
28				3	4	5	11	12
29				3	5	6	7	14
30				5	6	7	8	9
31
32
33
34
35
Sheet5

Thank you

Kind Regards,
Moti

 

[DanteAmor]

For that you can do it with the functionality of excel ordering by 5 levels:

 

[DanteAmor]

Try this macro:

Sub Macro4()
  Dim lr As Long

  lr = Range("D" & Rows.Count).End(3).Row
  With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("D6:D" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("E6:E" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("F6:F" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("G6:G" & lr), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("H6:H" & lr), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("D5:H" & lr)
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
End Sub

 

[motilulla]

For that you can do it with the functionality of excel ordering by 5 levels:

View attachment 92592

@Dante Amor. You are correct that I can do in excel 2010 but we don’t have option in excel 2000 so far need a macro sort them please is it possible?

Thank you

Kind Regards,
Moti