Column to Rows & Column

[fahadmalik09]

Hey Team,

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Mobile1[/TD]
[/TR]
[TR]
[TD]Address1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Mobile2[/TD]
[/TR]
[TR]
[TD]Address2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[/TR]
[TR]
[TD]Mobile3[/TD]
[/TR]
[TR]
[TD]Address3[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[/TR]
[TR]
[TD]Mobile 4[/TD]
[/TR]
[TR]
[TD]Address4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Name5[/TD]
[/TR]
[TR]
[TD]Mobile5[/TD]
[/TR]
[TR]
[TD]Address 5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to change this to

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]Name1[/TD]
[TD]Mobile1[/TD]
[TD]Address1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Mobile2[/TD]
[TD]Address2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Mobile3[/TD]
[TD]Address3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]Mobile4[/TD]
[TD]Address4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name5[/TD]
[TD]Mobile5[/TD]
[TD]Address5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am not an expert, i tried Transpose, concatenate; but i am not getting the result.

 

[hiker95]

fahadmalik09,

Here is a macro solution (that is based on your flat text displays) for you to try on a copy of your raw data workbook/worksheet.

I assume that your raw data is in column A, and, that the results will be written to columns C, D, and, E.

Sub fahadmalik09()
' fahadmalik09 - ME1077482 - hiker95, 11/13/2018
Dim Area As Range, s As Long, nr As Long
Application.ScreenUpdating = False
Columns("C:E").ClearContents
nr = 1
For Each Area In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    s = .Rows.Count
  End With
    Range("C" & nr).Resize(, s) = Application.Transpose(Area)
    nr = nr + 1
Next Area
Columns("C:E").AutoFit
Application.ScreenUpdating = True
End Sub

 

[sandy666]

another way with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name1[/td][td][/td][td=bgcolor:#E2EFDA]Name1[/td][td=bgcolor:#E2EFDA]Mobile1[/td][td=bgcolor:#E2EFDA]Address1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mobile1[/td][td][/td][td]Name2[/td][td]Mobile2[/td][td]Address2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Address1[/td][td][/td][td=bgcolor:#E2EFDA]Name3[/td][td=bgcolor:#E2EFDA]Mobile3[/td][td=bgcolor:#E2EFDA]Address3[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Name4[/td][td]Mobile 4[/td][td]Address4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name2[/td][td][/td][td=bgcolor:#E2EFDA]Name5[/td][td=bgcolor:#E2EFDA]Mobile5[/td][td=bgcolor:#E2EFDA]Address 5[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mobile2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Address2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name3[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mobile3[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Address3[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name4[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mobile 4[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Address4[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name5[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mobile5[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Address 5[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 4), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Calculated Modulo", each ([Index] <> 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter")
in
    #"Transposed Table"[/SIZE]