Copy/paste without #N/A's using only excel formulas

[espinozr]

Hello,


I have a table of data with numbers and some NA() values. I need to find a way to copy the rows with no NA() into another columns. Something like this:

	A	B
1	01/01/01	10
2	01/02/13	#NA
3	5/06/14	3
4	01/01/16	#NA
5	05/09/19	4
6	05/3/20	5

<tbody>
</tbody>

I should copy this data into columns D/E ignoring the rows with NA:

	D	E
1	01/01/01	10
2	5/06/14	3
3	05/09/19	4
4	05/3/20	5

<tbody>
</tbody>

I know I can filter, deselect NA and copy and paste the values, the problem is that I need to do this hundreds of times. Is there a way to do this using only excel formulas, without using a filter, or VB code?


Thanks,

 

[steve the fish]

Are those NA values true errors or text?

 

[espinozr]

Are those NA values true errors or text?

Those NA values are the result of an "IF" formula. Something like this IF(A>B, C, NA()).

 

[steve the fish]

Try:

=IFERROR(INDEX(A$1:A$6,SMALL(IF(NOT(ISERROR($B$1:$B$6)),ROW($B$1:$B$6)),ROWS($A$1:A1))),"")

Enter CTRL-SHIFT-ENTER.

 

[espinozr]

It's very strange. Your formula does work when I use a simple example like the one I showed above. However, when I tried to apply it to my real table, is not working. The formula ignores all the rows until the first "#N/A" appears. It does work for the following rows.

I then copied the data from my table into a new spreadsheet, and the formula does work... how is this possible? Is the same data, copied and pasted into another sheet, and now the formula works. Could it be a formatting issue in the data of the original table?

 

[espinozr]

This is what is happening, I hide all the unnecessary columns. As you can see the filter works perfectly after "18/06/2010", but is filtering all the rows between 9 to 15.

 

[espinozr]

I feel stupid, but I cannot find the edit button... Anyway, here is a link to a screenshot.

https://ibb.co/59Zs4cp

 

[steve the fish]

Ok if you arent starting from row 1 you need to add a little eg:

=IFERROR(INDEX(A$1:A$6,SMALL(IF(ISNUMBER($B$1:$B$6),ROW($B$1:$B$6)-ROW($B$1)+1),ROWS($A$1:A1))),"")

 

[espinozr]

You are my hero! Thank you so much, this works.