Forum Discussion

gty1992's avatar
gty1992
Copper Contributor
Nov 07, 2020

How to autofill a column with multiple values

Hi,

I have a spreadsheet with 5000 rows. Each group with a value and I need to fill down each cell with the name of the same family group. How can I do that?

 

Thanks

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Steel Contributor

    gty1992 

     

    I would like to suggest two possible methods, one is using Helper Data & Formula and second is VBA Macro.

     

    Method 1:

     

    Situation NAME column is blank.

     

    How it works:

    • Create Helper data as shown in AF2:AG5.
    • Formula in cell AC2:

     

    =IFERROR(VLOOKUP(AB2,$AF$2:$AG$5,2,FALSE),"")

     

     

    Method 2:

     

     

    • Hit Developer TAB, find and click DESIGN, left to it is INSERT.
    • From INSERT, ActiveX control section select & Draw COMMAND button.
    • Select the COMMAND button, Right click & from the Menu hit Property, find CAPTION and change it to  Fill Value ( Remember it's an optional).
    • Double click the COMMAND button to open VBA editor.
    • Copy & Paste this code.

     

    Private Sub CommandButton1_Click()
    
       For Each cell In Selection
        If cell.Value = "" Then
            cell.Value = cell.Offset(-1, 0).Value
        End If
    Next cell
    
    End Sub

     

    • Press ALT+Q to return to Sheet.
    • SAVE the Workbook as Macro Enabled ( *.xlsm ).
    • Select AD2:AD17 ( you need to select as needed ).
    • Now hit the COMMAND button.

     

    Macro will fill the blank cells as shown in Screen Shot above.

     

    • TheMaestro's avatar
      TheMaestro
      Copper Contributor

      Rajesh_Sinha 

      Thank you so much for providing the example in Method 2.  I have been trying to figure out something similar and this worked great.  

       

      Cheers,

      K

      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Steel Contributor
        Glad to help you,,, since Method 2 is working for you then you may accept my post as best answer as well like ☺
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    gty1992 This could be done as follows:

     

    1) Select the range B2:B5000 (or whatever your exact range may be)

    2) Press F5 (Go to, Special, Blanks)

    3) This should select all blank cells in the range with the active cell in B3

    4) Now, in =B2 and press Ctrl-Enter

    Edit: 4) Now, enter =B2 and press Ctrl-Enter

    5) All blank cells will now contain a formula pointing to the cell directly above it.

    6) Copy column B and paste as values on top of itself if you don't want to keep the formulae.

     

    Alternatively, use "Get & Transform Data" (a.k.a. Power Query "PQ") if this is recurring process on a data set that might be a bit more complex than what you described above. PQ has a standard function for "Fill Down"

    • Marra93's avatar
      Marra93
      Copper Contributor

      Riny_van_Eekelen Can you please clarify step 4?

      "4) Now, in =B2 and press Ctrl-Enter"

      My B2 is highlighted, but when I press Ctrl with the Enter, nothing happens?

       

      Help!  🙂

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        Marra93 I think it contains a typo. It should have read:

         

        "4) Now, enter =B2 and press Ctrl-Enter"

         

        What I meant to write is that when you have selected all the blank cells (step 3) and the active cell is B3, enter the formula =B2 and then Ctrl-Enter.

         

        Sorry about that. 

         

         

Resources