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

Question

Column A    to     Column B
0.83               0.83
0.6                0.6
#N/A               0.99
#N/A               0.93
0.99    
#N/A    
0.93    

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

Answer 1

I need to clarify something in @teylyn 's answer. The array formula should be copy-pasted into B1 in the above example (or the first cell of your desired data location for the output of the formula)... then hit C+S+E, then take that cell and drag it down the desired number of output cells - eg - 4 in the above example. This is a bit confusing above, and Microsoft's own help site doesn't help any users with this because it directs users to highlight output cells for any array formula, then type in your formula to the first cell, then hit C+S+E. I spent awhile trying to figure this out, and it was making my quite angry until I realized my very first step was wrong.

It won't work if you highlight, paste formula, C+S+E. It will only work if you paste, C+S+E, then drag the formula down or across your destination cells.

so something like this will work also:

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

or like this if your data is in column D:

=IFERROR(INDEX($D:$D,SMALL(IF(ISNUMBER($D:$D),ROW($D:$D)),ROW(D1))),"")

...but the final "row" reference must remain a relative reference, because as you drag it down your destination cells, it will populate the formula with D2, D3, D4, etc.

So, again, specifically: - highlight one destination cell - paste into it =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(A:A),ROW(A:A)),ROW(A1))),"") - control+shift+enter - highlight that cell again - drag the corner box down the desired number of rows/columns for your output cells

Answer 2

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

Answer 3

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--)

Answer 4

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.