comparing columns
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: comparing columns

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I often have two long columns of data, of varying length, that I need to compare. I need to take each cell in B, see if it matches any cells in A, and if it doesn't put the contents of B into C. I am new to excel and would appreciate any help. Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You might want to try the mixed Vlookup and IF functions:

    =if(iserror(vlookup(b1,$A$1:$A$10,1,false)),b1,vlookup(b1,$A$1:$A$10,1,false))

    The above formula is entered into c1, and copy it all the way down until there's no values in column b.

    Meaning of the above formula is, if looking up value in B1, exists in column A or range A1 to A10, then retrun the value from Column A, otherwise return the value from B1.

    Once the formula is copied down, the reference cell b1 should change, ie formula in c2 would reference to b2, so on.

    HTH

  3. #3
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry, BabyTiger, but I think what is required in C is the value in B if B is not in A, otherwise nothing. If so, try this
    =IF(ISNA(MATCH(B1,$A$1:$A$1000,0)),B1,"") in Cl.
    Change $A$1000 to the range you need, and copy down as far as the bottom of column B data.

    The again, I could be barking up the wrong tree.

    Richard

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com