Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
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?

share|improve this question
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 '13 at 18:42

3 Answers 3

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

share|improve this answer

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

share|improve this answer
    
+1 for the trick)))))) –  Peter L. Feb 14 '13 at 15:33

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.

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 1:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.