#N/A Values

[c0087]

1	1
#N/A	2
#N/A	3
2	4
3
#N/A
4

<tbody>
</tbody>

I have a bunch of #N/A values in columns. what's the easiest way to get the desired result (1234) in the adjacent column with no empty cells in between?

 

[Fluff]

How about

Book1
	A	B
2	1	1
3	#N/A	2
4	#N/A	3
5	2	4
6	3
7	#N/A
8	4
Master

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($A$2:$A$8<>"#N/A"),ROWS($A$1:$A1))),"")

 

[Rick Rothstein]

I'm not sure there is any way to characterize the "easiest way". If you do not need the data in Column B to be "live" (that is, changeable as values in Column A change), then this macro will avoid loading up the sheet with formulas...

Sub DeleteNAs()
  Columns("A").Copy
  Range("B1").PasteSpecial xlValues
  Columns("B").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp
End Sub

 

[c0087]

How about

	A	B
2	1	1
3	#N/A	2
4	#N/A	3
5	2	4
6	3
7	#N/A
8	4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Master​

Worksheet Formulas Cell Formula B2 =IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($A$2:$A$8<>"#N/A"),ROWS($A$1:$A1))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

this formula gets rid of the N/A's but leaves an empty cell

 

[c0087]

I'm not sure there is any way to characterize the "easiest way". If you do not need the data in Column B to be "live" (that is, changeable as values in Column A change), then this macro will avoid loading up the sheet with formulas...

Sub DeleteNAs()
  Columns("A").Copy
  Range("B1").PasteSpecial xlValues
  Columns("B").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp
End Sub

Thank you this works perfect if I do decide to go that route, but I'd still rather a formula for now.

 

[Fluff]

It doesn't leave any empty cells for me.
Can you please post some data where you are getting blank cells

 

[Sam_D_Ben]

Hi Fluff, Hi Rick,

How you been. A quick question. How about considering "Sort option" as the easiest way to get the desired result.

 

[Rick Rothstein]

How you been. A quick question. How about considering "Sort option" as the easiest way to get the desired result.

First, you would have to copy Column A values to Column B and then sort Column B as the OP wants the answer in the "adjacent column". Second, I assume you are implying to then select all of the #N/A cells and delete those cells (shifting the remaining values up). Yes, that might work depending on if the current order of the values needs to be maintained or not (the solutions so far preserves the current order whereas your suggestion would change their order).

 

[c0087]

It doesn't leave any empty cells for me.
Can you please post some data where you are getting blank cells

i made an error when copying in it ..

it works perfect! thank you so much

 

[Fluff]

You're welcome & thanks for the feedback