• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro for vertical to horizontal converting rows

syed abuthahir

New Member
Hi guys,

Can please give me the macro for vertical to horizontal converting rows for below attachment. i Mentioned input in 1st sheet and output should be like in 2nd sheet
 

Attachments

  • SAMPLE.xlsx
    10.2 KB · Views: 5
I can get each person's characteristics on one line as shown below. Will this work for you?
Data Range
A
B
C
D
E
F
G
H
I
J
K
L
M
1
Custom.Record No​
NAME​
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
2
1​
BALA​
PERSONALITY​
GOOD​
HOBBIES​
PLAYING CRICKET​
COUNTRY​
INDIAN​
GENDER​
MALE​
EDUCATION​
3
2​
VIJAY​
TYPE OF PERSON​
GOOD​
BEHAVIOUR​
GENDER​
COUNTRY​
MALE​
4
3​
VINOTH​
TYPE OF CHARACTER​
PLAYBOY​
GENDER​
MALE​
COUNTRY​
5
4​
VINO​
PERSONALITY​
GOOD​
HOBBIES​
PLAYING CRICKET​
COUNTRY​
INDIAN​
GENDER​
MALE​
EDUCATION​
JOB TYPE​
PERMANENT​

this was achieved in Power Query/Get and Transform which is on the Data Tab of Excel versions 2016 and later. For 2010 and 2013 you will need to go to MS website and download as an add-in.

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record No", Int64.Type}, {"NAME", type text}, {"Characteristic", type text}, {"Value", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record No", "NAME"}, "Attribute", "Value.1"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"NAME"}, {{"Data", each _, type table [Record No=nullable number, NAME=nullable text, Value.1=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Record No", "Value.1", "Index"}, {"Custom.Record No", "Custom.Value.1", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom.Record No", "NAME", "Custom.Value.1", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Value.1")
in
    #"Pivoted Column"
 

Attachments

  • SAMPLE.xlsx
    27.8 KB · Views: 0
Right-click the table on the Output sheet at cell A11 and choose Refresh to update that table.
 

Attachments

  • Chandoo44915.xlsx
    20.8 KB · Views: 4
Back
Top