#N/A Values

c0087

New Member
Joined
Jul 13, 2015
Messages
43
11
#N/A2
#N/A3
24
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?
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">#N/A</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">#N/A</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Master</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$8,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$A$2:$A$8</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>)/(<font color="Purple">$A$2:$A$8<>"#N/A"</font>),ROWS(<font color="Purple">$A$1:$A1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
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...
Code:
Sub DeleteNAs()
  Columns("A").Copy
  Range("B1").PasteSpecial xlValues
  Columns("B").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp
End Sub
 

c0087

New Member
Joined
Jul 13, 2015
Messages
43
How about

AB
211
3#N/A2
4#N/A3
524
63
7#N/A
84

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Jul 13, 2015
Messages
43
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...
Code:
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
It doesn't leave any empty cells for me.
Can you please post some data where you are getting blank cells
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 13, 2015
Messages
43
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 :mad:..

it works perfect! thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,785
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top