• 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.

Skip blanks when comparing two columns of data

marcus

New Member
Hello,

I apologize if this was answered in another post. I have searched the forums and cannot find a solution to this question. If you can recall other posts where this was answered feel free to direct me there.

I have two columns of data one of which I need to condense by excluding rows containing blank cells which are in another column.

For example, the data is (';' inserted to delineate columns):
Column A ; Column B
a ; [blank]
b ; 1
c ; 1
d ; [blank]
e ; 1

The desired result is:
Column C (on a different worksheet):
b
c
e

I know I can use autofilters which are simple. However, this ever-expanding file already has a several manual processes which I am trying to eliminate.

Some other information that may be of use is:
-The data range (ie number of rows) is approximately 80 and will likely expand
-Column A and B are not adjacent but are on the same worksheet
-The '1' values in Column B are the result of another formula (summing percentage weights to arrive at 100% or '1')

If possible I would like to avoid marcos.

Thank you for your advice.

Marcus
 
You can even try this - IFERROR(INDEX($A$1:$A$5,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$5)+($B$1:$B$5=""),0)),"")

put this in cell C2, using CTRL + SHIFT + ENTER and then drag it down
 
Thanks for the responses.

Azumi, the array formula works great. I will see if I can understand it for other applications as well.

Asheesh, The formula you provided returned the data from column A (that is, a, b, c, d, e). Any thoughts on this? Or perhaps I made a mistake.

Marcus
 
Back
Top