Excel Forum

Macro to format data

Faron Roth

Active Member

Members

Forum Posts: 3

Member Since:
January 20, 2023

Offline

January 20, 2023 - 9:49 am

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.

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 490

Member Since:
January 31, 2022

Offline

January 20, 2023 - 4:55 pm

You didn't attach a file. Please try again and press "Start upload" before submitting your reply.

Faron Roth

Active Member

Members

Forum Posts: 3

Member Since:
January 20, 2023

Offline

January 22, 2023 - 4:24 am

I have attached it here. Thanks for the follow up.

Alan Sidman

Steamboat Springs, CO

Member

Members

Trusted Members

Forum Posts: 150

Member Since:
October 18, 2018

Offline

January 22, 2023 - 12:58 pm

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"

James1989

Active Member

Members

Forum Posts: 5

Member Since:
January 27, 2023

Offline

January 27, 2023 - 6:52 pm

Hi,

Do you need to fill in Blanks in Column A ...???

Sub FilinBlanks()
Range("A2:A14").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub