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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01/02/13[/TD]
[TD]#NA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5/06/14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]01/01/16[/TD]
[TD]#NA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]05/09/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]05/3/20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/01[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5/06/14[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]05/09/19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05/3/20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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.