Sorting multiple columns help

[bobby_smith]

Good day,
I'm looking for an easy way to sort cross tabulated data by column.
I was able to get the desired results by using custom sort and adding each column at a time. This will not be efficient as I will have lots of columns.
Any feedback will be greatly appreciated.
See images for original ( before) and desired results (after)

 

[sandy666]

maybe use XL2BB to post the example data instead of HTML
and update your profile (Account details) about Excel version

 

[bobby_smith]

Updated and I'm using Excel 365
Before

test 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item	January	February	March	April	May	June	July	August	September	October	November	December
2	Item 1								1,189.60	20,568.10	27,665.64	12,640.55	414,705.65
3	Item 2												2,144.25
4	Item 3									35,763.65	214,571.14	321,788.22	631,022.27
5	Item 4							1,353.31	1,477.51				12,850.00
6	Item 5	5,800.56					48,815.32	70,764.38	23,455.80	82,500.00
7	Item 6								67.62	12,897.40	4,314.16
8	Item 7							4,224.00	4,291.62
9	Item 8							1,389.64	1,737.05
10	Item 9		2,978.98	95,299.11	93,118.99	14,271.39	10,465.86	68,405.26	44,517.10	21,598.45	39,438.79	37,363.87	55,101.46
11	Item 10						156,556.42	207,944.91	93,041.43	48,014.57	5,928.60		94,134.80
12	Item 11										2,703.62	922.73	762.66
13	Item 12												593.18
14	Item 13							1,617.00	5,219.63	7,504.01	2,814.10		57,858.88
15	Item 14												21,780.00
16	Item 15					2,897.17	64,227.63	63,831.57	66,610.73	61,531.80	57,437.61	35,422.47	43,645.00
17	Item 16							3,991.44	4,221.78
18	Item 17									12,443.42	8,096.13	16,747.59	47,092.19
Sheet1

After

test 1.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Item	January	February	March	April	May	June	July	August	September	October	November	December
2	Item 5	14,501.4					122,038.3	176,911.0	58,639.5	206,250.0
3	Item 9		7,447.5	238,247.8	232,797.5	35,678.5	26,164.7	171,013.2	111,292.8	53,996.1	98,597.0	93,409.7	137,753.7
4	Item 15					7,242.9	160,569.1	159,578.9	166,526.8	153,829.5	143,594.0	88,556.2	109,112.5
5	Item 10						391,391.1	519,862.3	232,603.6	120,036.4	14,821.5		235,337.0
6	Item 4							3,383.3	3,693.8				32,125.0
7	Item 8							3,474.1	4,342.6
8	Item 13							4,042.5	13,049.1	18,760.0	7,035.3		144,647.2
9	Item 16							9,978.6	10,554.5
10	Item 7							10,560.0	10,729.1
11	Item 6								169.1	32,243.5	10,785.4
12	Item 1								2,974.0	51,420.3	69,164.1	31,601.4	1,036,764.1
13	Item 17									31,108.6	20,240.3	41,869.0	117,730.5
14	Item 3									89,409.1	536,427.9	804,470.6	1,577,555.7
15	Item 11										6,759.1	2,306.8	1,906.7
16	Item 12												1,483.0
17	Item 2												5,360.6
18	Item 14												54,450.0
Sheet1

 

[sandy666]

Great, thanks for all above

 

[sandy666]

look at Sorting Multiple Columns

 

[Dossfm0q]

Hi
May this help, your feed back is highly appreciated.

Thanks

Sub Sorting_multiple_columns()

Dim Sht As Worksheet
Dim Rng As Range, MyRange As Range

Set Sht = ThisWorkbook.Worksheets("Sheet1")
Set MyRange = Worksheets("Sheet1").Range("A1:M18")
For Each Rng In MyRange.Cells(1, 2).Resize(1, MyRange.Columns.Count - 1)
'Count Empty Range
CER = WorksheetFunction.CountA(Range(Cells(lRow + 2, Rng.Column).Address & ":" & Cells(MyRange.Rows.Count, Rng.Column).Address))
If CER <> 0 Then

    Sht.sort.SortFields.Clear
    Sht.sort.SortFields.Add2 Key:=Rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sht.sort
        .SetRange Range("A" & IIf(lRow = "", 2, lRow + 1) & ":M20")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    lRow = Cells(Rows.Count, Rng.Column).End(xlUp).Row
    Rows(Cells(1, 1).Row & ":" & lRow).EntireRow.Hidden = True
End If

Next
MyRange.EntireRow.Hidden = False
MyRange.Offset(1, 0).EntireRow.Hidden = False
End Sub

sortmul.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item	January	February	March	April	May	June	July	August	September	October	November	December
2	Item 1								1189.6	20568.1	27665.64	12640.55	414705.7
3	Item 2												2144.25
4	Item 3									35763.65	214571.1	321788.2	631022.3
5	Item 4							1353.31	1477.51				12850
6	Item 5	5800.6					48815.32	70764.38	23455.8	82500
7	Item 6								67.62	12897.4	4314.16
8	Item 7							4224	4291.62
9	Item 8							1389.64	1737.05
10	Item 9		2978.98	95299.1	93119	14271.4	10465.86	68405.26	44517.1	21598.45	39438.79	37363.87	55101.46
11	Item 10						156556.4	207944.9	93041.4	48014.57	5928.6		94134.8
12	Item 11										2703.62	922.73	762.66
13	Item 12												593.18
14	Item 13							1617	5219.63	7504.01	2814.1		57858.88
15	Item 14												21780
16	Item 15					2897.17	64227.63	63831.57	66610.7	61531.8	57437.61	35422.47	43645
17	Item 16							3991.44	4221.78
18	Item 17									12443.42	8096.13	16747.59	47092.19
Sheet1

After

sortmul.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Item	January	February	March	April	May	June	July	August	September	October	November	December
2	Item 5	5800.6					48815.32	70764.38	23455.8	82500
3	Item 9		2978.98	95299.1	93119	14271.4	10465.86	68405.26	44517.1	21598.45	39438.79	37363.87	55101.46
4	Item 15					2897.17	64227.63	63831.57	66610.7	61531.8	57437.61	35422.47	43645
5	Item 10						156556.4	207944.9	93041.4	48014.57	5928.6		94134.8
6	Item 4							1353.31	1477.51				12850
7	Item 8							1389.64	1737.05
8	Item 13							1617	5219.63	7504.01	2814.1		57858.88
9	Item 16							3991.44	4221.78
10	Item 7							4224	4291.62
11	Item 6								67.62	12897.4	4314.16
12	Item 1								1189.6	20568.1	27665.64	12640.55	414705.7
13	Item 17									12443.42	8096.13	16747.59	47092.19
14	Item 3									35763.65	214571.1	321788.2	631022.3
15	Item 11										2703.62	922.73	762.66
16	Item 12												593.18
17	Item 2												2144.25
18	Item 14												21780
Sheet1

 

[bobby_smith]

@Dossfm0q

Thank you very much, the code works just as I needed.

To anyone reusing this code for, you will need to make changes to the items in red box in the attached pic.
Change 1 to the name of the current sheet with the data to me sorted
Change 2 to the name of the current sheet with the data to me sorted
Change 3 to range the data to be sorted in
Change 4 to the last cell in your range

 

[Dossfm0q]