Log In
Register
Home
Active Member
January 20, 2023
Offline
I have a need to have a macro to format and build out data. My original data will look like the following. I have attached a sample to this post.:
Code ID
100a
10118
10128
12042
12686
100c
100i
10083
10190
10192
10216
10227
What I need to happen is that the code value is copied to the left cell to that of an ID number. The number of rows can change based upon the output of data to ecel. The need to flatten the data is so that I can have build pivot charts and tables. In some cases there will be a Code and no ID's to go with it. In those cases a empty row will be displayed under the Code. That row would need to be removed.
Copy data down in a macro I have done, but it stops when it gets to the next Code.
Any help would be appreciated. I cannot change the original format of the data so need to address it after it gets into Excel.
Moderators
January 31, 2022
Offline
You didn't attach a file. Please try again and press "Start upload" before submitting your reply.
Active Member
January 20, 2023
Offline
I have attached it here. Thanks for the follow up.
Trusted Members
October 18, 2018
Offline
If I am understanding your needs correctly, then an easy means is with Power Query versus VBA
Mcode
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filled Down" = Table.FillDown(Source,{"Code"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([ID] null))
in
#"Filtered Rows"
1 Guest(s)
