Hello community.
I am here because i am desperate to find an answer.
I have some data....in each column, numbers have to be sorted in ascending order, independently one column from the other..
The problem is a have 1600 columns to sort, and i cant find a solution to make it possible.
Please help me.
Last edited by rhesus3000; 07-09-2022 at 12:21 PM.
Please yellow banner on how to attach a sample workbook: it suggests a VBA solution which will "loop" through the columns and sort each one indivdually.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Data starts in column A
Sub sortColumns() Dim sRng As Range, skey As Range Application.ScreenUpdating = False lr = Cells(Rows.Count, "A").End(xlUp).Row ' Change "A" to start Column lc = Cells(2, Columns.Count).End(xlToLeft).Column scol = 1 ' change 1 to start column number (A=1, B=2 etc) For c = scol To lc Set sRng = Range(Cells(1, c), Cells(lr, c)) Set skey = Range(Cells(1, c), Cells(1, c)) sRng.Sort Key1:=skey, Order1:=xlAscending, Header:=xlNo Next c Application.ScreenUpdating = True End Sub
Last edited by JohnTopley; 07-09-2022 at 12:32 PM.
YEAAAAH!!! PERFECT!!!saved me!!
Power Querylet Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each 1*1), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Custom"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Column","",Replacer.ReplaceText,{"Attribute"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Custom"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}}), #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Attribute", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute"}, {{"Count", each _, type table}}), #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Attribute", Order.Ascending}}), #"Added Custom1" = Table.AddColumn(#"Sorted Rows1", "Custom", each [Count][Value]), #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"Count"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1","Custom",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18", "Custom.19", "Custom.20"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}, {"Custom.4", Int64.Type}, {"Custom.5", Int64.Type}, {"Custom.6", Int64.Type}, {"Custom.7", Int64.Type}, {"Custom.8", Int64.Type}, {"Custom.9", Int64.Type}, {"Custom.10", Int64.Type}, {"Custom.11", Int64.Type}, {"Custom.12", Int64.Type}, {"Custom.13", Int64.Type}, {"Custom.14", Int64.Type}, {"Custom.15", Int64.Type}, {"Custom.16", Int64.Type}, {"Custom.17", Int64.Type}, {"Custom.18", Int64.Type}, {"Custom.19", Int64.Type}, {"Custom.20", Int64.Type}}), #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}), #"Transposed Table" = Table.Transpose(#"Removed Columns2") in #"Transposed Table"
Please mark as SOLVED: see "Thread Tools" at top of page.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks