0
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?

1
  • 2
    Why not use autofilter and filter for "does not equal: #N/A"? the excel autofilter does know errors like #N/A, #DIV/0 or #NAME.
    – nixda
    Feb 11, 2013 at 18:42

4 Answers 4

1

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

1

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

0
1

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

enter image description here

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.

6
  • This is something like what I'm looking for (I need a formula to avoid copy/pasting), but this particular formula is not working. For one, the IF statement as written is incomplete. What is the value_if_false? Also, it seems that this is set up to return a blank cell ("") if the corresponding cell in A1:A10 is an error, but I want the new list to just continue on to the next number and remove all errors.
    – user197426
    Feb 12, 2013 at 17:23
  • As I said above, the formula may need tweaking to suit your needs. The IF statement is not inclomplete, since the False parameter is optional. And yes, it will return a blank cell in case of an error, so that the last rows with this formula don't show error values. If you don't know how many N/A values there are, the formula needs to be copied down as many rows as there are values in the original data to cater for the possibility of all values being good. ...
    – teylyn
    Feb 12, 2013 at 22:20
  • ... The error trap just makes sure that there will be no errors shown for the last few rows of the formula, where no value is returned from the original. Be a bit more specific about your data set, then I can update my answer with a more specific formula.
    – teylyn
    Feb 12, 2013 at 22:21
  • You seem to have misunderstood the formula completely. It does NOT return a blank for each error value in the original data. Have you tried it at all? Create a data set in A1 to A10, put the formula in B1 (using Ctrl-Shift-Enter) and then copy down to B10.
    – teylyn
    Feb 12, 2013 at 22:23
  • I have been playing with the formula, and I was able to get it to work if my list starts at A1, but not if it starts in a different column (obviously I changed A1:A10 to the appropriate cells). I have a list that starts at H25. Do I need to add something about columns to the formula? I don't have a great understanding of array formulas, and the index and row functions seem to be confusing me as well. Without a good understanding of the formula, I can't seem to modify it to suit my needs.
    – user197426
    Feb 13, 2013 at 1:19
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .