Formula to skip the blank rows or "N/A".


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 26th March 2014, 09:30
xllearner's Avatar
xllearner xllearner is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Can use Formulae
 
Join Date: Jun 2010
Location: india
Posts: 20
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (8) xllearner is on a distinguished road
India
Default Formula to skip the blank rows or "N/A".
     Excel: Functions   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: Functions   

Hello,

I am looking for a formula to skip the blank rows or "N/A". When i use vlookup for the below example, few rows will have "N/A", is there any way that we can skip the "N/A" and find next result

Below is the example.

ID Name ID
123 John 2021
456 Mary
789 Sheila 789
1011 Daniel
1213 David 1415
1415 Adam
1617 Philip
1819 Lewis 1617
2021 Robert 1819


Result should be
Robert
Sheila
Adam
Philip
Lewis

Not like

Robert
#N/A
Sheila
#N/A
Adam
#N/A
#N/A
Philip
Lewis
#N/A
#N/A
#N/A


Last edited by NBVC; 26th March 2014 at 15:29. Reason: added the F to formula in the title


The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #2  
Old 26th March 2014, 15:28
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: ormula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Assuming your data is currently in columns A:C, then try:

My Array Formula:
=IFERROR(INDEX($B$2:$B$10,SMALL(IF(ISNUMBER($C$2:$C$10),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($A$2:$A2))),"")

This is an Array formula. Type or paste the formula into the cell(s) as directed, then press F2 to ensure the cell is active. Hold the Ctrl and Shift keys down and then press ENTER.
This is often referred to as committing using CSE. You should see curly { } brackets around the formula. Copy the formula down or across as needed.
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel



  #3  
Old 27th March 2014, 07:38
xllearner's Avatar
xllearner xllearner is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Can use Formulae
 
Join Date: Jun 2010
Location: india
Posts: 20
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (8) xllearner is on a distinguished road
India
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Thanks a lot NBVC, this is exactly what i was searching and it works perfect.

I was looking for this formula to include it in VBA coding.

I have another request, based on number of ID's in C column it inserts the row.

Can you please help me with VBA code that this formula automatically applied on the newly inserted rows



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Old 27th March 2014, 08:16
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,707
Posts Per Day: 0.82
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (70) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Why look for a formula to use in VBA?
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me



  #5  
Old 27th March 2014, 08:28
xllearner's Avatar
xllearner xllearner is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Can use Formulae
 
Join Date: Jun 2010
Location: india
Posts: 20
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (8) xllearner is on a distinguished road
India
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Roy,

now i need VBA to apply this formula automatically applied on the newly inserted rows.



  #6  
Old 27th March 2014, 09:58
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,707
Posts Per Day: 0.82
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (70) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Which version of Excel are you using? Formulas can be inserted automatically without VBA
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me



The Code Cage Advertisment
Advertisement
  #7  
Old 28th March 2014, 10:03
xllearner's Avatar
xllearner xllearner is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Can use Formulae
 
Join Date: Jun 2010
Location: india
Posts: 20
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (8) xllearner is on a distinguished road
India
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Roy,

I am using MS 2007



  #8  
Old 29th March 2014, 08:01
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,707
Posts Per Day: 0.82
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (70) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Why not convert the data to a Table and then you can have calculated column
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me



  #9  
Old 31st March 2014, 08:58
xllearner's Avatar
xllearner xllearner is offline
Junior Member
MS Office Version: MS Office 2003
MS Office Skill Level: Can use Formulae
 
Join Date: Jun 2010
Location: india
Posts: 20
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (8) xllearner is on a distinguished road
India
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Roy,

I cannot have calculated tables as i have merged cells in the sheet.

Please help me with code or formula to automatically fill the formula to the newly inserted rows



The Code Cage Advertisment
Advertisement
  #10  
Old 31st March 2014, 10:01
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,707
Posts Per Day: 0.82
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (70) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: Formula to skip the blank rows or "N/A".

------ Register to get rid of these "In Post" ads! ------


Another reason not to use Merged Cells. What's the benefit of using merged cells against all the benefits of Excel Tables?
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me



The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
blank, n or a, ormula, rows, skip

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 07:43.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.20205 seconds
  • Memory Usage 6,638KB
  • Queries Executed 17 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (5)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (10)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (10)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (10)postbit_ctoptip
  • (10)postbit_onlinestatus
  • (59)postbit_reputation
  • (10)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (5)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete