auto fill blanks in multiple columns with formula or macro

[markman235]

Hi everyone,

I have a sheet with several hundred line items. Columns A - I are only populated at the start of each unique PO.

So if i have a PO with 5 line items, columns A - I would have the PO data in row 1, but 2 - 5 would be blank. Row 6 would be populated with the data from the next PO.

Is there an easy way with either a macro or formula to get these columns to copy data to the blank cells?

Thanks everyone,

Mark

 

[alansidman]

I can think of two ways to do this. One would be using VBA and the other would use Power Query/Get and Transform. If you can post some sample data showing what your worksheet would look like, I am sure that someone here (me included) can offer up a viable solution that will work for you.

 

[markman235]

Thanks Allen, I need all the help i can get.

This is the sheet i am working with.

[TABLE="width: 642"]
<colgroup><col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="2"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 98, bgcolor: transparent"]Division
[/TD]
[TD="width: 58, bgcolor: transparent"]Number[/TD]
[TD="width: 53, bgcolor: transparent"]ID[/TD]
[TD="width: 58, bgcolor: transparent"]Number[/TD]
[TD="width: 65, bgcolor: transparent"]Day Code[/TD]
[TD="width: 54, bgcolor: transparent"]Time[/TD]
[TD="width: 45, bgcolor: transparent"]Status[/TD]
[TD="width: 75, bgcolor: transparent"]Date[/TD]
[TD="width: 58, bgcolor: transparent"]Number[/TD]
[TD="width: 58, bgcolor: transparent"]Number[/TD]
[TD="width: 99, bgcolor: transparent"]UPC[/TD]
[TD="width: 61, bgcolor: transparent"]Quantity[/TD]
[TD="width: 69, bgcolor: transparent"]Quantity[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent, align: right"]755
[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]17[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]11479[/TD]
[TD="width: 65, bgcolor: transparent, align: right"]9[/TD]
[TD="width: 54, bgcolor: transparent, align: right"]11:48[/TD]
[TD="width: 45, bgcolor: transparent"]D[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]<st1:date ls="trans" month="12" day="2" year="2018">12/2/2018</st1:date> [/TD]
[TD="width: 58, bgcolor: transparent, align: right"]68021[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]303[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050266[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]228[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]228[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]304[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050268[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]350[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]350[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]607[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050279[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]114[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]114[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2932[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050312[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]140[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]140[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2933[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050313[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]114[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]114[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2935[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050315[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]456[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]456[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2937[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050317[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]200[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]57130[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050072[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]114[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]114[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent, align: right"]755
[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]17[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]11497[/TD]
[TD="width: 65, bgcolor: transparent, align: right"]9[/TD]
[TD="width: 54, bgcolor: transparent, align: right"]11:48[/TD]
[TD="width: 45, bgcolor: transparent"]D[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]<st1:date ls="trans" month="12" day="4" year="2018">12/4/2018</st1:date> [/TD]
[TD="width: 58, bgcolor: transparent, align: right"]68470[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]303[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050266[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]342[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]342[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]304[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050268[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]420[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]420[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2932[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050312[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]350[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]350[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2933[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050313[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]228[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]228[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 65, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"][/TD]
[TD="width: 45, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent, align: right"]2935[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050315[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]684[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]684[/TD]
[/TR]
[TR]
[TD="width: 98, bgcolor: transparent, align: right"]755
[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]17[/TD]
[TD="width: 53, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]11498[/TD]
[TD="width: 65, bgcolor: transparent, align: right"]9[/TD]
[TD="width: 54, bgcolor: transparent, align: right"]11:48[/TD]
[TD="width: 45, bgcolor: transparent"]D[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]<st1:date ls="trans" month="12" day="4" year="2018">12/4/2018</st1:date> [/TD]
[TD="width: 58, bgcolor: transparent, align: right"]68471[/TD]
[TD="width: 58, bgcolor: transparent, align: right"]344[/TD]
[TD="width: 99, bgcolor: transparent, align: right"]1001111050323[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]70[/TD]
[TD="width: 69, bgcolor: transparent, align: right"]70[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

If you use Power Query, first convert your range of data to a table and name it Table3. Then.....

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", Int64.Type}, {"Number", Int64.Type}, {"ID", Int64.Type}, {"Number2", Int64.Type}, {"Day Code", Int64.Type}, {"Time", type number}, {"Status", type text}, {"Date", type datetime}, {"Number3", Int64.Type}, {"Number4", Int64.Type}, {"UPC", Int64.Type}, {"Quantity", Int64.Type}, {"Quantity5", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Division", "Number", "ID", "Number2", "Day Code", "Time", "Status", "Date", "Number3"})
in
    #"Filled Down"

 

[Fluff]

Or with a macro

Sub MyFillDown()
With Range("A2", Range("J" & Rows.Count).End(xlUp).Offset(, -1))
   .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   .Value = .Value
End With
End Sub

 

[markman235]

Thank you Alan!!

 

[markman235]

Thank you Fluff!!!

 

[Fluff]

You're welcome

 

[Declamatory]

If I understand it each blank cell in your range would equal the cell above. I don't think you need a macro for this although it is quicker I expect.

If you select your data then right click and press CTRL+G
Click on Special and tick 'Blanks' then press OK. All your blank cells in the range are selected.
Then press = then your up arrow
Press CTRL and ENTER.