• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fill the blanks with data above the blank cells

anishms

Member
Is there any shortcut or macro which can help in filling the highlighted blank cells with the data above the blank?. The complete data has 50K + rows of data.

64448
 

Attachments

  • chandoo.xlsx
    10.5 KB · Views: 3
Select the full range.
In Find & Select , Go To Special -> Blanks
64451
The empty cells are selected.
Type this formula in A3 (simply start typing the formula, do not select the cell. It is preselected by the previous step): = A2
Press CTRL + ENTER
 
rahulshewale1
1) turning off screenupdating
2) solve used rows and columns
3) check each row,column combination
... if length of cell value is less than 1 then add above cells value to that cell
4) turning on screenupdating
 
Using Power Query, you can do this quickly. Here is the Mcode for that:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SubCls_Name", type text}, {"Test_CD", type any}, {"Service_Description", type text}, {"Disc%", type number}, {"Invoice Count", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"SubCls_Name", "Test_CD", "Service_Description"})
in
    #"Filled Down"
 

Attachments

  • chandoo.xlsx
    20.4 KB · Views: 3
Back
Top