Forum Discussion

sashakorniakUK's avatar
sashakorniakUK
Copper Contributor
Dec 03, 2020

Excel formula function or macro? Data Migration for Horizontal Columns to Vertical Columns

Excel formula function or macro? Data Migration for Horizontal Columns to Vertical Columns

 

This must be a common problem? 

 

I have old database exported to csv.

The Old columns are;

 

First Name Surname DOB 1 Address

 

The new database format - Columns

 

 

1 First name 1 Surname 1 DOB 1 1 Address 2 First name 2 Surname 2 DOB 3 First name 3 Surname 3 DOB 4 First name 4 Surname 4 DOB 5 First name 5 Surname 5 DOB

 

                

 

I have over 6500 Row of data to migration.

 
1 First name1 Surname1 DOB 1 1 Adresss2 First name2 Surname2 DOB 3 First name3 Surname3 DOB 4 First name4 Surname4 DOB 5 First name5 Surname5 DOB 

Columns from above formatted better

 

I need an Automated process to move the data? So would this be formula function or macro?

 

Here is the As Is format

 

First NameSurnameDOB1 address
AlanSmith01/01/19591 Murton
Gavin Smith02/01/19591 Murton
Justin Smith03/01/19591 Murton
Bill Smith04/01/19591 Murton
KateSmith05/01/19591 Murton
TonyJones06/01/19592 Murton
LeeJones07/01/19592 Murton
OliviaJones08/01/19592 Murton
NoahKing09/01/19593 Murton 
OliverSMITH10/01/19594 Murton
WilliamJOHNSON11/01/19594 Murton
ElijahWILLIAMS12/01/19594 Murton
JamesJONES13/01/19594 Murton
BenjaminBROWN14/01/19594 Murton
LucasDAVIS15/01/19597 Murton
MasonMILLER16/01/19597 Murton
EthanWILSON17/01/19597 Murton
EmmaMOORE18/01/19597 Murton
AvaTAYLOR19/01/19598 Murton
SophiaANDERSON20/01/19598 Murton
IsabellaTHOMAS21/01/19598 Murton
CharlotteJACKSON22/01/19599 Murton
AmeliaWHITE23/01/19599 Murton
MiaHARRIS24/01/195910 Murton
HarperMARTIN25/01/195910 Murton
EvelynTHOMPSON26/01/195910 Murton
 

 

Example 1

 

Here is the TO BE Format

 

 
1 First name1 Surance1 DOB 1 1 Adresss2 First name2 Surance2 DOB 3 First name3 Surance3 DOB 4 First name4 Surance4 DOB 5 First name5 Surance5 DOB 
AlanSmith01/01/19591 MurtonGavin Smith02/01/1959Justin Smith03/01/1959Bill Smith04/01/1959KateSmith05/01/1959
MiaHARRIS24/01/195910 MurtonHarperMARTIN25/01/1959EvelynTHOMPSON26/01/1959      
TonyJones06/01/19592 MurtonLeeJones07/01/1959OliviaJones08/01/1959      
NoahKing09/01/19593 Murton             
OliverSMITH10/01/19594 MurtonWilliamJOHNSON11/01/1959ElijahWILLIAMS12/01/1959JamesJONES13/01/1959BenjaminBROWN14/01/1959
LucasDAVIS15/01/19597 MurtonMasonMILLER16/01/1959EthanWILSON17/01/1959EmmaMOORE18/01/1959   
AvaTAYLOR19/01/19598 MurtonSophiaANDERSON20/01/1959IsabellaTHOMAS21/01/1959      
CharlotteJACKSON22/01/19599 MurtonAmeliaWHITE23/01/1959         

 

Example 2

I am currently just using the filter by 1 address to copy and paste the data.

Any thoughts or feedback would be greatly appreciated!

I have a sample excel file if needed.

excel
Formulas and Functions
Macros and VBA

10 Replies

  • sashakorniakUK's avatar
    sashakorniakUK
    Copper Contributor
    Dec 04, 2020

    sashakorniakUK adversi 

     

    I have been using power query to find a solution

    So far I found this works, however I not find my 65000 rows of data has more than 5 names per address. However I need to either add names 6+ to a new row or stop that a source etc.

     

    here the code so far

     

    let

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Surance", type text}, {"DOB", type text}, {"1 address", type text}}),

        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"1 address"}, "Attribute", "Value"),

        #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"1 address"}, {{"Count", each _, type table}}),

        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Value],",##,")),

        #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",##,", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15"}),

        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type datetime}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type datetime}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type datetime}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type datetime}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type datetime}}),

        #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Count"}),

        #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom.3", type date}, {"Custom.6", type date}, {"Custom.9", type date}, {"Custom.12", type date}, {"Custom.15", type date}})

    in

        #"Changed Type2"

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 04, 2020

      sashakorniakUK 

      Same idea, but without hardcoding column names

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(
        Source,
        {"address"},
        {
            {"Tables",
             each
[   #"Removed Columns" = Table.RemoveColumns(_,{"address"}),
    #"Added Index" = Table.AddIndexColumn(
        #"Removed Columns",
        "Index", 1, 1, Int64.Type
    ),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
        #"Added Index",
        {"Index"}, "Attribute", "Value"
    ),
    #"Merged Columns" = Table.CombineColumns(
        Table.TransformColumnTypes(
            #"Unpivoted Other Columns",
                {{"Index", type text}}, "en-GB"
            ),
            {"Attribute", "Index"},
            Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"
    ),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Transposed Table1",
        [PromoteAllScalars=true]
    )
][#"Promoted Headers"]
            },
            {"Count", each Table.RowCount(_), Int64.Type}
        }
    ),
    // Generate Column Names
    MaxRows = List.Max(#"Grouped Rows"[Count]),
 
    RemoveAddress = Table.RemoveColumns(Source,{"address"}),
    #"Kept First Rows" = Table.FirstN(RemoveAddress,1),
    #"Added Custom" = Table.AddColumn(#"Kept First Rows", "Index", each {1..MaxRows}),
    #"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Index", {"Index"}, "Attribute", "Value"),
    GetTogether = Table.CombineColumns(
        Table.TransformColumnTypes(
            #"Unpivoted Other Columns",
            {{"Index", type text}}, "en-GB"),{"Attribute", "Index"},
            Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnNames"
    ),
    ColumnNames = GetTogether[ColumnNames],
 
 
    #"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", ColumnNames)
in
    #"Expanded Tables"

       

      • sashakorniakUK's avatar
        sashakorniakUK
        Copper Contributor
        Dec 04, 2020

        SergeiBaklan  - I cannot download the file?

         

        testing the code shortly.

         

        Any thoughts on the issue with if an address has more than 5 names?

  • adversi's avatar
    adversi
    Iron Contributor
    Dec 04, 2020

    sashakorniakUK 

    VBA or Power Query would probably be the best approach. Can you share your workbook to further test the dataset? 

Resources