• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros for No blank cells data.

srinidhi

Active Member
Hi I have the vlookup result in column c & D . The result is like this in some cells it is #N/A & in some cells it is numbers.

My question is in column E & F I want to display only the numbers one below the other from column C & D , & the number of cells in column c & D will increases day by day.


I request you to suggest me some formula or provide me with a macro code for the same. Thanking you in advance.
 
If Columns C&D have numbers in them, except for the errors you can use Count(), as Count skips the #N/A error


If Columns C&D have text in them try the following array formula,

=COUNT(IF(ISTEXT(Range),1))

Enter with Ctrl Shift Enter

Change the range to suit


If it is a mix of text and numbers try the following array formula

=COUNTA(Range)-COUNT(IF(ISERROR(Range),1))
 
Hey Thanks, But I don't want to count, I want the numbers in C & D to be reflected in column E & F
 
Sorry about the misread ?


What about =IFERROR(Cx,"") and =IFERROR(Dx,"")

where x is the row No


or if you have an older Excel version

=IF(ISERROR(Cx),"",Cx)
 
Back
Top