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

[wdrer93]

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:

Brand	Title	CreatorName	Brand Amount	Year 1	Year 1 $ Total	Year 2	Year 2 $ Total	Years Remaining
A	Landscape Analysis	Gottard,Gary	$25,000.00	2020	$25,000.00	2021	$0.00	0
B	Social Listening	Norton,Melissa	$15,000.00	2020	$7,500.00	2021	$7,500.00	0
C	In-home Testing	Meyers,Tim	$60,000.00	2020	$20,000.00	2021	$20,000.00	1
D	In-home testing	Miller,Amanda	$40,000.00	2020	$20,000.00	2021	$20,000.00	0

The data above shows multiple years in a single row, making it hard to break out into a pivot table in any meaningful way. It also shows the number of years remaining, which means that the rest of the Brand Amount value should be allocated to the number of remaining years there are.

Here is how I would like to reformat it in another sheet where it shows the $s that are going to be spent that year:

Brand	Title	CreatorName	Brand Total $	Current Year $	Year
A	Landscape Analysis	Gottard,Gary	$25,000.00	$25,000.00	2020
B	Social Listening	Norton,Melissa	$15,000.00	$7,500.00	2020
B	Social Listening	Norton,Melissa	$15,000.00	$7,500.00	2021
C	In-home Testing	Meyers,Tim	$60,000.00	$20,000.00	2020
C	In-home Testing	Meyers,Tim	$60,000.00	$20,000.00	2021
C	In-home Testing	Meyers,Tim	$60,000.00	$20,000.00	2022
D	In-home testing	Miller,Amanda	$40,000.00	$20,000.00	2020
D	In-home testing	Miller,Amanda	$40,000.00	$20,000.00	2021

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 with relative ease 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

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA: How to convert single row in one Worksheet to multiple rows in a different Worksheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[wdrer93]

Hi Fluff,

Thanks for the heads up. I have just been warned against doing (or at least not providing where else it had been cross-posted) that at the site you mentioned above. Sorry for that! Will not happen again.

I did receive advice to fix my problem using PowerQuery using the 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"

(sorry if this is another format I should be using to post code)

Thank you!
Will