I have a data table with 4 columns. I want to join together the data in columns B, C and D and have the result in column E sorted in Alphabetical order. I can solve this with a Function, but I would prefer to solve via a formula.
I was thinking of using SORT with TEXTJOIN
I've tried =TEXTJOIN(", ",TRUE,SORT(B2:D2)) but that fails
A Power Query Solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Region", Order.Ascending}, {"Value", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "0"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
@robbieg You can use last optional argument of the SORT function to indicate that the data is in a row across columns.
Thank you Alan. And thank you for showing me Modulo for creating an 'index' for pivoting on. I will be able to use that again.
A followup please:
How do i cope if col3 is empty? I seem to get the wrong result
Thank you. I was using SORT incorrectly. Lesson learnt.
If column 3 is blank, then the Mcode will sort on the remaining two columns. See the example attached
Thank you for the followup, Alan.
