Results 1 to 9 of 9

Thread: Comparing two columns - what formula to use

  1. #1

    Comparing two columns - what formula to use



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to compare two columns - one has 187 cells with data, another has 204 cells with data, entries are text in both the columns, some cells do not have any data and some cells are repeated with same data randomly. I am trying to find out what are the data in second column that is not there in the first one. I am sure that second column has some data that is not there in the first column. Please suggest what formula I should use. Thank you very much for your help!

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Assuming columns A and B:
    =isnumber(MATCH(B1,A:A,0))
    will return TRUE if B1 matches a value in column A and FALSE otherwise. You can then copy this down as far as necessary.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3
    Hi JoePublic,
    Thanks for your prompt response. I tried with the formula but it did not work, it gives value FALSE where ever blanks are there. There are many blanks in both columns and they are spaced out randomly, so it is not possible to get the solution. Is there a way I can define the blanks? Thanks again for your help!

    Ajoy
    Last edited by Ajoy; 2013-04-04 at 03:36 PM.

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Interesting-what response did it give then?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    It gives TRUE response when what ever is there in B matches with column A, but for any row where both columns have blanks it gives FALSE response.

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    But you said
    I am trying to find out what are the data in second column that is not there in the first one
    and that is what that formula gives you, is it not?
    Circumference of a circle = 2πrē



    ēthe circle's radius

  7. #7
    You are correct, but there are many blanks in between and the blanks are randomly spaced. So I am getting too many FALSE and hence cannot find which cells are there in column B that are not there in A. Wish I could attach a sample file for you to see, you would know what I mean.

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Perhaps a simple change to:
    =IF(B1="","",isnumber(MATCH(B1,A:A,0)))
    Circumference of a circle = 2πrē



    ēthe circle's radius

  9. #9
    Thank you very much, it worked perfectly!

Tags for this Thread

Posting Permissions

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