I am trying to combine 2 columns of names into one using a formula. The problem is the list of names changes so the range size changes according to who signs up every week.
The first list (list A) may have 50 names one week then 45 the next.
The second list (list B) may have 10 names one week then 15 the next.
All the names are different so I don't need to worry about duplicates.

I have a formula on each row that brings the names from another sheet according to what group they are signed up for. So, the lower rows that don't show names just have a space ("") in them.
I want it to work for up to 75 rows without having to change the formula.
So column A would show 50 names and have the "" in the remaining 25.
Column B would show 10 names and have the "" in the remaining 65.
I want column C to just show the 60 names without any blank rows in between them.