Reputation: 2407

Move Data in Vertical Cells To Horizontal Cells in Excel 2007

I am using excel 2007

I have a excel sheet with around 1200 records with following structure...

WHAT CAN BE EASIEST WAY TO DO THIS ?

For easy understanding, Adding image :

Upvotes: 0

Answers (3)

Dr M L M J

Reputation: 2407

Formula Used as follows (Giving solution here so in future members can use it)

The formula is : In empty cell after Treasurer Column,

 =If($a2="","",a2) // copy over next 4 columns to give the District, Branch, Pre name, address, mobile elements as =If($a2="","",a2), =If($b2="","",b2), =If($c2="","",c2), =If($c2="","",c3), =If($c2="","",c3), =If($c2="","",c4)

 =if($d2="","",d2) // copy over next 2 columns for Secretary details like =if($d2="","",d2), =if($d2="","",d3), =if($d2="","",d4)

 =if($g2="","",g2) // copy over next 2 columns for Treasurer details like =if($g2="","",g2), =if($g2="","",g3), =if($g2="","",g4)

Now Select All New Formula Cells in a row after Treasurer column >> Drag Down Till All Records....

Then Copy all these down to the bottom of your data

Either copy / paste special >> values to somewhere else and

Then sort by District / Branch / Pres to drop the blank rows

Upvotes: 1

Ron Rosenfeld

Reputation: 60354

As per your comment request, here is a Power Query solution.

To enter the code:

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.

Algorithm

Fill in (fill down) the blank rows for the District and Branch columns
Group by District and Branch
For each Group, extract as a delimited string the entries for President, Secretary and Treasurer.
Create the appropriate column names and split the delimited strings into separate columns.

If you have more officers, or more items per officer/position, or more columns before you get to the officer columns, it should be relatively simple to modify the code to take that into account.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"District", Text.Type}, {"Branch", type text},
         {"President", type text}, {"Secretary", type text}, {"Treasurer", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"District", "Branch"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"District", "Branch"},{
        {"President", each Text.Combine([President],";")},
        {"Secretary", each Text.Combine([Secretary],";")},
        {"Treasurer", each Text.Combine([Treasurer],";")}
        }),

colHeaderSuffix = {"","Addr","Mobile"},
PresidentCols = List.Accumulate(colHeaderSuffix, {}, (state, current) => List.Combine({state, {"President " & current}})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "President", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), PresidentCols), 

SecretaryCols = List.Accumulate(colHeaderSuffix, {}, (state, current) => List.Combine({state, {"Secretary " & current}})),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter", "Secretary", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), SecretaryCols),

TreasurerCols = List.Accumulate(colHeaderSuffix, {}, (state, current) => List.Combine({state, {"Treasurer " & current}})),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Treasurer", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), TreasurerCols) 

in
    #"Split Column by Delimiter3"

Original Data