Hi all!
Is there a way I can stack data that are in multiple rows/columns with a formula? I need it to dynamic and updates as the changes are made.
If I have a range like below. (Sorry about the size, it wouldn't stay when I try to resize it)
| a | ||||
| b | h | i | ||
| c | j | |||
| d | ||||
| g | e | |||
| f |
And I want the results to be like (order isn't important, I'd probably use the sort function anyways.)
| a |
| b |
| c |
| d |
| e |
| f |
| g |
| h |
| i |
| j |
Thanks in advance!
If you happen to be an MS365 subscriber AND an Insider with access to recently rolled-out functions, this one will do it for you:
=SORT(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",TRUE,A:E),",")))
An alternative is to unpivot your data with Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value", Order.Ascending}})
in
#"Sorted Rows"
If you have the new Insider beta functions, it's actually even simpler:
=SORT(TOCOL(A1:E6,1))
for example.
