Transformation of Multicolumns

[KarthickDijo]

Hi All,

How can the 8 columns be transferred to two columns as shown in the attachment. XL2BB isn't working on my side, so attaching in image format.

Thanks in advance !!

 

[ouvay]

Hello,

You'll have to put the headers in yourself... but here is a rough outline.

Sub assimilate_V1()
    For i = 1 To 8 Step 2
        Cells(2, 10).Offset(Cells(Rows.Count, 10).End(3).Row - 1).Resize(Cells(Rows.Count, i).End(3).Row, 2).Value = Cells(2, i).Resize(Cells(Rows.Count, i).End(3).Row, 2).Value
    Next i
End Sub

 

[ouvay]

Here is how it should work

 

[ouvay]

Sub assimilate_V1()
    For i = 1 To 8 Step 2
        rnk = rnk + 1
        Cells(2, 12).Offset(Cells(Rows.Count, 10).End(3).Row - 1).Resize(Cells(Rows.Count, i).End(3).Row - 1).Value = rnk
        Cells(2, 10).Offset(Cells(Rows.Count, 10).End(3).Row - 1).Resize(Cells(Rows.Count, i).End(3).Row, 2).Value = Cells(2, i).Resize(Cells(Rows.Count, i).End(3).Row, 2).Value
    Next i
End Sub

Revision to add Rank as in your example image

 

[KarthickDijo]

Thanks for solution !! However, am wondering is there any solution using PQ ??

 

[ouvay]

Thanks for solution !! However, am wondering is there any solution using PQ ??

I'm rubbish at PQ.. maybe someone well-versed at PQ can help

 

[Automatrix]

I'm rubbish at PQ.. maybe someone well-versed at PQ can help

Probably not so elegant, but here is one PQ solution.

Merge the four pairs of columns using a suitable delimiter, e.g. Colour§Red, Brand§Nokia.
Merge these four columns into one using another delimiter, eg. Colour§Red$Brand§Nokia.
Split this column into rows by $.
Finally, split the column into new columns by §.

 

[KarthickDijo]

Probably not so elegant, but here is one PQ solution.

Merge the four pairs of columns using a suitable delimiter, e.g. Colour§Red, Brand§Nokia.
Merge these four columns into one using another delimiter, eg. Colour§Red$Brand§Nokia.
Split this column into rows by $.
Finally, split the column into new columns by §.

Sorry and Thanks a lot for the solution.. It's working !! Just one thing i need that rank as well.

 

[alansidman]

A bit long winded but see if this works for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Name1", "Value1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Name2", "Value2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Name3", "Value3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Name4", "Value4"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged4"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Merged1", "Merged2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged5"),
    #"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"Merged5", "Merged3", "Merged4"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged6"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns5", {{"Merged6", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged6"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Merged6", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Merged6.1", "Merged6.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter1",{{"Merged6.1", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Merged6.2] <> "")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","","Location",Replacer.ReplaceValue,{"Merged6.1"}),
    #"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"Merged6.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each if[Merged6.1]= "Color" then 1 else if [Merged6.1]= "Brand" then 2 else if [Merged6.1]="Location" then 3 else 4),
    #"Sorted Rows2" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}})
in
    #"Sorted Rows2"

 

[KarthickDijo]

A bit long winded but see if this works for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Source,{"Name1", "Value1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Name2", "Value2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Name3", "Value3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Name4", "Value4"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged4"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Merged1", "Merged2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged5"),
    #"Merged Columns5" = Table.CombineColumns(#"Merged Columns4",{"Merged5", "Merged3", "Merged4"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged6"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns5", {{"Merged6", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged6"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Merged6", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Merged6.1", "Merged6.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter1",{{"Merged6.1", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Merged6.2] <> "")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","","Location",Replacer.ReplaceValue,{"Merged6.1"}),
    #"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"Merged6.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each if[Merged6.1]= "Color" then 1 else if [Merged6.1]= "Brand" then 2 else if [Merged6.1]="Location" then 3 else 4),
    #"Sorted Rows2" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}})
in
    #"Sorted Rows2"

Thanks a lot !!