1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel-matching up columns

Discussion in 'Business Applications' started by JayJoJeans, Nov 16, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. JayJoJeans

    JayJoJeans Thread Starter

    Joined:
    Nov 5, 2009
    Messages:
    29
    This should be a piece of cake, I just know it! But me and excel never see eye to eye. I have a simple document with column a and column b. I want to find the matching ones and create a column C with the ones that are not matched. Can I do that? Without using copy paste for three hours?? I attached the file below.

    Thanks!!
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,896
    First Name:
    Wayne
    you could use this formula
    =ISNA(MATCH(B2,$A$1:$A$260,FALSE)

    I have an example with this in column C and then if missing in columnD

    It could be tidied up a bit

    BUT is that the output you want in D
     

    Attached Files:

  3. JayJoJeans

    JayJoJeans Thread Starter

    Joined:
    Nov 5, 2009
    Messages:
    29
    what I am looking to do is compare A to B with spaces in column B for the ones in A that are not in B.

    Then take the ones in A that are not in B and create C

    Does that make sense?
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,896
    First Name:
    Wayne
    sorry dont understand

    you want to align the entries
    so if you have
    Code:
    A                  B
    1                  1
    2                  3
    3
    4
    


    you want a result to be produces
    A B C
    1 1 2
    2 - 4
    3 3
    4

    BTW in my example I got the true/false the wrong way around
     

    Attached Files:

  5. JayJoJeans

    JayJoJeans Thread Starter

    Joined:
    Nov 5, 2009
    Messages:
    29
    I want to match B to A with spaces for the ones that are not in B but are in A, then all the ones in A that are not in B put in C

    LOL this is confusing ME! ;P
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,896
    First Name:
    Wayne
    still confused
    because I dont know where you start the comparison ....

    I assume The Entries in B will have to move down the rows until they align
    as i showed

    you start with

    A - B
    1 - 3
    2 - 1
    3 -
    4 -

    and then you need to take the 3 in Column B and move it next to the 3 in column A
    Take the 1 in column B and move it up next to the 1 in column A
    then create a New column C
    and put a 2 in column C next to the 2 in A
    and put a 4 in column C next to the 4 in A

    A - B - C
    1 - 1
    2 - - - 2
    3 - 3
    4 - - - 4

    I have had to add the dashes to stop the forum from removing the spaces
     
  7. JayJoJeans

    JayJoJeans Thread Starter

    Joined:
    Nov 5, 2009
    Messages:
    29
    yes! that's it! Sorry I am not very good at explaining it well.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,896
    First Name:
    Wayne
    Well I think I have done it - BUT kept column A and B as original
    and then in column

    D E F

    reproduced what you wanted to see in
    A B C
    ---------- check the attached spreadsheet and make sure its correct - before we move onto the Not in A question

    I think, BUT if its in B and NOT in A - what happens ie

    A - B
    1 - 3
    2 - 1
    3 -
    4 -

    becomes

    A - B - C
    1 - 1
    2 - - - 2
    3 - 3
    4 - - - 4

    NOW what if, as in your example, we start with

    A - B
    1 - 3
    2 - 1
    3 - 7
    4 -

    where does the 7 go - as its NOT in A
     

    Attached Files:

  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    This is probably similar to what etaf worked out for you. However, it is not really that accurate, for you have names entered in different ways, and they will not match.
    For instance, you have WVTM-TV 13, Birmingham and WVTM NBC 13 Birmingham both in the listings and they do not match up. I also used the TRIM() function on your lists, for you had some that didn't match due to things like spaces at the beginning or end, and "ABC " is not equal to "ABC" as far as Excel is concerned. :eek:
     

    Attached Files:

  10. JayJoJeans

    JayJoJeans Thread Starter

    Joined:
    Nov 5, 2009
    Messages:
    29
    Thanks everyone...I realized the problem and went in and cleaned it up. I think I got it the way I need it. Thank you for your help!!

    JJJ
     
  11. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Refreshed for JJJ to mark this as unsolved and re-post
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/877765

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice