How can I condense a list that has missing data to a new list without the cells with missing data?

Your question wasn't specific on how often you want to do this or how many columns you'll have to do this for. Here's the quick and dirty method. If you need something for lots of columns then it might be better to use vba

1. Select the column that includes the data. Add a filter (data menu->filter).
2. select the drop down for the list and uncheck the checkbox for the error
3. Select the first cell in the column.
4. Press CTRL+SHIFT+DOWN to select the other visible cells
5. Press CTRL+C to copy
6. Move to a new sheet in the workbook (or press SHIFT+F11 to create a new one)
7. Press CTRL+V to paste the data without the error

Note: for older versions of excel you'll have to use a "custom filter" from the drop down menu of the filter instead ("does not equal" error). You can find a quick into here (look under the heading "Excel Custom AutoFilters").

@Nixda: yikes.. I didn't notice you had given basically the same answer in your comment until I refreshed the page after answering

In addition to tiktok's Autofilter solution (which is certainly more flexible), this is another "quick&dirty" fix that is slightly shorter:

1. Select column A (Ctrl-Space)
2. Home->Find&Select->Goto (Ctrl-G)->Special (Alt-S)
3. Depending if its formulas (Alt-F) or constants (Alt-O), select either option and uncheck Errors (Alt-E)
4. Copy (Ctrl-C) - this will select every cell that is not an error.
5. Select the target (e.g. cell B1) and paste (Ctrl-V)

Done!

If you don't want a copy of the data but rather only delete the errors, you can alternatively modify step 3 to only select errors and then simply delete them ((Ctrl--)

Or with a formula (tweak to your needs). Start in B1 and copy down.

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROW(A1))),"")

This is an array formula and must be confirmed with Ctrl+Shift+Enter

If the data is not in A1 to A10 but, say, H25 to H34, use

=IFERROR(INDEX($H$25:$H$34,SMALL(IF(ISNUMBER($H$25:$H$34),ROW($A$1:$A$10)),ROW(A1))),"")

Note how the formula still shows Row($A$1:$A$10). This part will return a number between 1 and 10 for valid entries, and that is the number that INDEX needs to pull the correct row.

Also, the last Row(A1) stays the same, because that returns numbers from one upwards and will be used to find the smallest (1), second smallest (2), third smallest (3), etc. value.