let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Data", each _, type table [Name=text, #"Sale%"=number, Country=text, Rank=number, Sub=text, Ticket Numbers=number, Lan=text, Cities=text, School=text, Home=text, Colour=text, Road=text, Email ID=text]}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Ticket Numbers"}, {"Data.Ticket Numbers"})
in
#"Expanded Data"
Sub Macro1()
[Input!A1].CurrentRegion.AdvancedFilter xlFilterCopy, , [A1:B1], True
[A1].CurrentRegion.Sort [A1], xlAscending, [B1], , xlAscending, Header:=xlYes
End Sub
• First correct the cell Output!B1 which must be exactly the same header name than in worksheet Input.• Paste this VBA beginner starter (like any Excel beginner operating manually with the Macro Recorder) to the Output worksheet module(or in a standard module but then Output must be the active worksheet when this VBA procedure is launched) :Code:Sub Macro1() [Input!A1].CurrentRegion.AdvancedFilter xlFilterCopy, , [A1:B1] [A1].CurrentRegion.Sort [A1], xlAscending, [B1], , xlAscending, Header:=xlYes End Sub
Do you like it ? So thanks to click on bottom right Like !
Using Power Query, here is an alternative means:
Code:let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Data", each _, type table [Name=text, #"Sale%"=number, Country=text, Rank=number, Sub=text, Ticket Numbers=number, Lan=text, Cities=text, School=text, Home=text, Colour=text, Road=text, Email ID=text]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Ticket Numbers"}, {"Data.Ticket Numbers"}) in #"Expanded Data"
this code just copy and details from input to output.