How to autofill a column with multiple values

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.