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

wdrer93

New Member
Joined
Jun 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

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:
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 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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,082
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

Power Query:
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
 
Last edited by a moderator:

Forum statistics

Threads
1,148,421
Messages
5,746,581
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top