Transforming Data Table

[li chen]

 Have a sheet of data as below Col1/ Col2...NN

  Col1 (Txt)           Col 2..NN

----------              -- -- -- -- -- 

abc c1:v1            5   23   102

xyz  cx:vz          23   101 5   

vav  cN:VU     103  23   5   

what is the easy way to transform into the below?

col1      Col2          Col3         Col4

5       abc c1:v1   xyz  cx:vz   vav  cN:VU 

23     abc c1:v1   xyz  cx:vz   vav  cN:VU 

101   xyz  cx:vz 

102   abc c1:v1

103   vav  cN:VU 

[#2]

Hi

Hope this will help you.

https://support.office.com/en-us/article/TRANSPOSE-function-ED039415-ED8A-4A81-93E9-4B6DFAC76027

[#3]

Li Chen,

if you can use Power Query then this would be the (not optimized) code.

let
 Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
 #"Replaced Value" = Table.ReplaceValue(Source," ","#",Replacer.ReplaceText,{"Txt"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Txt", type text}, {"NN", type text}}),
 #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "NN", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"NN.1", "NN.2", "NN.3"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"NN.1", Int64.Type}, {"NN.2", Int64.Type}, {"NN.3", Int64.Type}}),
 #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Txt", "Index"}, "Attribut", "Wert"),
 #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribut"}),
 #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE")[Index]), "Index", "Txt"),
 #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
 #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Zusammengeführt", Text.Trim, type text}}),
 #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Zusammengeführt", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Zusammengeführt.1", type text}, {"Zusammengeführt.2", type text}, {"Zusammengeführt.3", type text}}),
 #"Replaced Value1" = Table.ReplaceValue(#"Changed Type2","#"," ",Replacer.ReplaceText,{"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
 #"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Zusammengeführt.1", "1"}, {"Zusammengeführt.2", "2"}, {"Zusammengeführt.3", "3"}, {"Wert", "NN"}})
in
 #"Renamed Columns"