Copy Range from one sheet to another and sort

Good day to you all,

I'm sure this is a simple task, and I've tried, but can't seem to find the magic formula. I've searched this forum for something similar and the internet but I am unable to find what I want. I can do it in Google Sheets, but I can't seem to get it to work in Excel 2016.

I want to grab a range from one sheet, and have it show up on another sheet, sorted with either no blanks or the blanks sorted to the bottom of the range.

This is my google formula:
=FILTER(INPUT!D2:D14,ISBLANK(INPUT!D2:D14)=FALSE)

Thanks for any help, and have a wonderful day.

Below is the code for the task you had given:

NB: This is considering that there is a sheet named Sheet2(where you want the range to be sorted with blanks at the bottom). And your input is on the page named "Input"

------------------------------------------------------------------------------------

Sub RangeSort()
'
' RangeSort Macro
'
Sheets("Sheet2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(Input!RC[3]="""","""",VALUE(Input!RC[3]))"
Range("A2:A14").Select
Selection.FillDown
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A14") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A2:A14")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I tried to get this to work, by putting the script activation on the "Shadow List" column, beginning in Cell E1. It failed as it started with a Blank Cell. If possible, I'd also like this to be an active script not having to be launched via Alt+F8. (I'm trying to make this Sheet Supervisor Proof)

My end request for this to work correctly, is depending on Staffing I have for the day, they are manually typed into Column A, A2:A14. If only 10 employee's show up, then there will be 3 blanks on the bottom (not an issue). I have trainee's (Shadows) show up from time to time to have O.J.T. with a seasoned employee, as assigned in the Shadow Staff Column C on the same row as as the assigned Trainer. Then I'd like to have the Shadow Staff copied and sorted in Column E under the Shadow List A2:A14 with blanks removed as show in the attached image. Lastly, I'd like to have the end result in Column A, A17:A29, with the Staff combined as shown.

I got all of this to work on Google Sheets, but am trying to learn Excel as well.

Again, I appreciate the above VB Script, I just don't know how to edit it beyond the obvious variable.

Thank you again, and much appreciated.

Excel_1.png