• 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.

VBA: How to convert single row in one Worksheet to multiple rows in a different Worksheet

Will Drerup

New Member
Hi all,

I'm quite new to VBA and have been struggling with this problem for a while now. I receive data in a way that makes it hard to put into a pivot table, and would like to create a macro that would reformat the data to then make it easier to understand. I need to take one row of an excel file and transform it into multiple rows in a different sheet.

Here is the example of how I receive the data:
BrandTitleCreatorNameBrand AmountYear 1Year 1 $ TotalYear 2Year 2 $ TotalYears Remaining
ALandscape AnalysisGottard,Gary$25,000.002020$25,000.002021$0.000
BSocial ListeningNorton,Melissa$15,000.002020$7,500.002021$7,500.000
CIn-home TestingMeyers,Tim$60,000.002020$20,000.002021$20,000.001
DIn-home testingMiller,Amanda$40,000.002020$20,000.002021$20,000.000

And here is how I would like to reformat it in another sheet:
BrandTitleCreatorNameBrand Total $Current Year $Year
ALandscape AnalysisGottard,Gary$25,000.00$25,000.002020
BSocial ListeningNorton,Melissa$15,000.00$7,500.002020
BSocial ListeningNorton,Melissa$15,000.00$7,500.002021
CIn-home TestingMeyers,Tim$60,000.00$20,000.002020
CIn-home TestingMeyers,Tim$60,000.00$20,000.002021
CIn-home TestingMeyers,Tim$60,000.00$20,000.002022
DIn-home testingMiller,Amanda$40,000.00$20,000.002020
DIn-home testingMiller,Amanda$40,000.00$20,000.002021

There are surely problems that I'm not taking into consideration, so maybe this is tougher than it seems. But it feels like a problem that could be solved relatively easily by someone experienced in VBA.

Also, this is my first post- so if I have left out crucial information or am not following community guidelines in any way please let me know and I will correct the post.

Thanks!
Will
 

vletm

Excel Ninja
Will Drerup
As written in Forum Rules
Please post, new posts in the correct forums
There are more advices
How to get the Best Results at Chandoo.org
... eg an Excel-sample file.

Please reread those Forum Rules.
In this time - I'm moving this to correct Forum.
 

AlanSidman

Well-Known Member
Here is a solution using Power Query

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Title", type text}, {"CreatorName", type text}, {"Brand Amount", Int64.Type}, {"Year 1", Int64.Type}, {"Year 1 $ Total", Int64.Type}, {"Year 2", Int64.Type}, {"Year 2 $ Total", Int64.Type}, {"Years Remaining", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Years Remaining"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Brand", "Title", "CreatorName"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Amount", each if Text.Contains([Attribute],"$") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each not Text.Contains([Attribute], "Total")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Brand Amount", each if Text.Contains([Attribute], "Brand") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Brand Amount"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Brand Amount")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Brand", "Title", "CreatorName", "Brand Amount", "Attribute", "Value", "Amount"})
in
    #"Reordered Columns"
Data Range
A
B
C
D
E
F
G
8
Brand​
Title​
CreatorName​
Brand Amount​
Attribute​
Value​
Amount​
9
A​
Landscape Analysis​
Gottard,Gary​
25000​
Year 1​
2020​
25000​
10
A​
Landscape Analysis​
Gottard,Gary​
25000​
Year 2​
2021​
0​
11
B​
Social Listening​
Norton,Melissa​
15000​
Year 1​
2020​
7500​
12
B​
Social Listening​
Norton,Melissa​
15000​
Year 2​
2021​
7500​
13
C​
In-home Testing​
Meyers,Tim​
60000​
Year 1​
2020​
20000​
14
C​
In-home Testing​
Meyers,Tim​
60000​
Year 2​
2021​
20000​
15
D​
In-home testing​
Miller,Amanda​
40000​
Year 1​
2020​
20000​
16
D​
In-home testing​
Miller,Amanda​
40000​
Year 2​
2021​
20000​
 

Will Drerup

New Member
Hi Alan,

Thank you so much for the help. I don't know how to use Power Query but I'll look into how I can leverage your solution.

And yes- I realized that I probably should not have posted this in another forum. Won't happen again!

Thank you,
Will
 

AlanSidman

Well-Known Member
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Top