+ Reply to Thread
Results 1 to 20 of 20

comparing 2 columns

  1. #1
    Registered User
    Join Date
    05-30-2005
    Posts
    19

    Question comparing 2 columns

    I would like to compare cells in column B with cells in column A. If cell in column B is found in column A, corresponding cell in column C will display message, else "Not Found!".

    My problem lies in the fact that text in column B is not exactly equal to text in column A.

    I have read other threads on this but none tackle the text strings not being exactly equal.

    Example;
    Cell A1: QWERT ASDF
    Cell B1: QWERTYU ASDFG

    Any help really appreciated

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    What should be the result for the example you provided. Should it be not found. If otherwise, on what parameters do you want to match the 2 texts?

    Mangesh

  3. #3
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    Suppose I have the following;


    [A1] QWE RTYU [B1] QWE RT [C1] QWE RTYU
    [A2] ASD FGH [B2] ASD F [C2] ASD FGH
    [A3] QAZ WSX [B3] GHJ KL [C3] 'QAZ WSX' NOT FOUND

    Column A will always be larger (greater # of rows) than column B.
    Cells that do not match I would like to use shading to indicate. (I think I will use conditional formatting for this purpose).

    I have being using the 'VLOOKUP' function, but this is not really suitable for my needs. Ultimately, I want the file to be user-friendly and automated.

    I have also tried using 'IF(COUNTIF(RANGE,CELL)>0,"",CELL & "NOT FOUND")'.
    Last edited by PLPE; 05-30-2005 at 07:47 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,749
    Hi,

    UDF

    Use like in cell
    =wfind(A1,B$1:B$10)

    Please Login or Register  to view this content.

  5. #5
    Nigel
    Guest

    Re: comparing 2 columns

    Your example is they are NOT the same! So what is the condition(s) that
    allow a string to match?

    Is it spaces embedded or at beginning or end, or parts of strings etc.....

    Exact matches are easy - in-exact matches need clear conditions

    --
    Cheers
    Nigel



    "PLPE" <PLPE.1pud6g_1117451107.397@excelforum-nospam.com> wrote in message
    news:PLPE.1pud6g_1117451107.397@excelforum-nospam.com...
    >
    > I would like to compare cells in column B with cells in column A. If
    > cell in column B is found in column A, corresponding cell in column C
    > will display message, else "Not Found!".
    >
    > My problem lies in the fact that text in column B is not exactly equal
    > to text in column A.
    >
    > I have read other threads on this but none tackle the text strings not
    > being exactly equal.
    >
    > Example;
    > Cell A1: QWERT ASDF
    > Cell B1: QWERTYU ASDFG
    >
    > Any help really appreciated
    >
    >
    > --
    > PLPE
    > ------------------------------------------------------------------------
    > PLPE's Profile:

    http://www.excelforum.com/member.php...o&userid=23856
    > View this thread: http://www.excelforum.com/showthread...hreadid=375010
    >




  6. #6
    Nigel
    Guest

    Re: comparing 2 columns

    Are you saying that 'any' string in column B has to match the left (variable
    length) 'any' string in column A - if it does put column A string into
    column C, if not then put column A string & "not found" in column C - or is
    it a row by row comparison?

    --
    Cheers
    Nigel



    "PLPE" <PLPE.1pufyc_1117454705.9261@excelforum-nospam.com> wrote in message
    news:PLPE.1pufyc_1117454705.9261@excelforum-nospam.com...
    >
    > Suppose I have the following;
    >
    >
    > [A1] QWE RTYU [B1] QWE RT [C1] QWE RTYU
    > [A2] ASD FGH [B2] ASD F [C2] ASD FGH
    > [A3] QAZ WSX [B3] GHJ KL [C3] 'QAZ WSX' NOT FOUND
    >
    > Column A will always be larger (greater # of rows) than column B.
    > Cells that do not match I would like to use shading to indicate. (I
    > think I will use conditional formatting for this purpose).
    >
    > I have being using the 'VLOOKUP' function, but this is not really
    > suitable for my needs. Ultimately, I want the file to be user-friendly
    > and automated.
    >
    > I have also tried using 'IF(COUNTIF(RANGE,CELL)>0,"",CELL & "NOT
    > FOUND")'.
    >
    >
    > --
    > PLPE
    > ------------------------------------------------------------------------
    > PLPE's Profile:

    http://www.excelforum.com/member.php...o&userid=23856
    > View this thread: http://www.excelforum.com/showthread...hreadid=375010
    >




  7. #7
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    Column A & B do not match exactly, but are pretty close.
    Originally, both columns had '_' & '@' included, but I added macros to getr rid of these - easier to do comparisons (methinks!).

    Here are some of my entries;

    [Col A];
    RD INP LKG 0V
    RD INP LKG 5V25

    [Col B];
    RD INP LKG 5V25 nA
    CS INP LKG 5V25 nA

    [Col C];
    RD INP LKG 5V25 - Found
    CS INP LKG 5V25 nA - Not Found


    {I think Jindon has me on the right track, but it's still not working for me!}

  8. #8
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    Quote Originally Posted by jindon
    Please Login or Register  to view this content.
    I've been banging around with this code and the idea behind it for the day. It will simply return "txt1 Found", regardless of whether txt2 is present or not.

    Any other ideas? ♦♦

  9. #9
    Jim May
    Guest

    Re: comparing 2 columns

    In Cell C1 enter:
    =IF(ISNUMBER(FIND(B1,A1)),A1,A1&" Not Found")
    and copy down.
    HTH

    "PLPE" <PLPE.1pulic_1117461904.3583@excelforum-nospam.com> wrote in message
    news:PLPE.1pulic_1117461904.3583@excelforum-nospam.com...
    >
    > Column A & B do not match exactly, but are pretty close.
    > Originally, both columns had '_' & '@' included, but I added macros to
    > getr rid of these - easier to do comparisons (methinks!).
    >
    > Here are some of my entries;
    >
    > [Col A];
    > RD INP LKG 0V
    > RD INP LKG 5V25
    >
    > [Col B];
    > RD INP LKG 5V25 nA
    > CS INP LKG 5V25 nA
    >
    > [Col C];
    > RD INP LKG 5V25 - Found
    > CS INP LKG 5V25 nA - Not Found
    >
    >
    > {I think *Jindon* has me on the right track, but it's still not working
    > for me!}
    >
    >
    > --
    > PLPE
    > ------------------------------------------------------------------------
    > PLPE's Profile:

    http://www.excelforum.com/member.php...o&userid=23856
    > View this thread: http://www.excelforum.com/showthread...hreadid=375010
    >




  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,749
    PLPE

    added 3rd argument to determine number of words to be compared.
    e.g.
    =wfind(b1,a$1:a$10,3)
    will compare first 3 words
    Please Login or Register  to view this content.
    Quote Originally Posted by PLPE
    Column A & B do not match exactly, but are pretty close.
    Originally, both columns had '_' & '@' included, but I added macros to getr rid of these - easier to do comparisons (methinks!).

    Here are some of my entries;

    [Col A];
    RD INP LKG 0V
    RD INP LKG 5V25

    [Col B];
    RD INP LKG 5V25 nA
    CS INP LKG 5V25 nA

    [Col C];
    RD INP LKG 5V25 - Found
    CS INP LKG 5V25 nA - Not Found


    {I think Jindon has me on the right track, but it's still not working for me!}

  11. #11
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Hi PLPE,

    enter the following formula in cell C1 and copy down:
    =IF(ISNUMBER(FIND(B1,A1)),A1,A1&" not found")

    Mangesh

  12. #12
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    Thanks for all the replies.
    Unfortunately, I can't try them out until tomorrow afternoon.
    As soon as i do, i'll get back with results.

    Thank you

  13. #13
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    Quote Originally Posted by jindon
    PLPE

    added 3rd argument to determine number of words to be compared.
    e.g.
    =wfind(b1,a$1:a$10,3)
    will compare first 3 words
    Please Login or Register  to view this content.
    This code will return "Found" regardless of what it is passed to it {even empty cells}. Any chance you could add in a few comments so I can see exactly where you're coming from with this Jindon?

    Thanks again

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,749
    Hi,

    Since I wasn't sure how many words to compare from your sample data, added 3rd argument to determine.

    e.g
    in cell
    =wfind(A1:B1:B10,3)

    The code will:

    go through B1 to B10 testing if there is a cell which matches number of words from the left.

    if A1 contains AA BBB CCC nn (will test up to CCC, since the 3rd arg is 3)

    B1 AA BBB nn (not found)
    B2 AA BBB CCC DDD FFF (found)
    B3 AA CCC BBB nn (not found)
    B4 AA BBB CCC dd (found)
    B5 BB AAA CCC (not found)
    B6 CC BBB AAA dd (not found)
    B7 AAA BBB CCC QQ (found)
    .
    .
    .

    Please Login or Register  to view this content.
    Quote Originally Posted by PLPE
    Hi Jindon,

    Thanks for the input on my problem. I've looked at your code & it seems to return "Found" for every input. For example; I typed "hello world" in C1 and used your code to find it in a range (where it did not exist), & it returned "hello world Found".

    Any chance of adding a few comments to the code so I can see where you are coming from?

    Thanks,
    Jerry

  15. #15
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    This is working a lot better now, thanks Jindon.
    I still have a few mismatches, namely due to >3 words in the name.
    These I can use conditional formatting on and have the user intervene to rectify.

    The end product will be able to take in files of different column depths from 2 files, compare them and highlight any differences -> basically to minimise user time and hence cost.

    Thanks.

  16. #16
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    how much more complicated would this UDF get if I were to add a 4th argument???

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,749
    HI,

    The thing I need is a CLEAR LOGIC

    If you can provide us a logic that we can encode, the problem are solved already.

    rgds,
    jindon

  18. #18
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    what's a CLEAR LOGIC?

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,749
    Hi,

    The rules of how to test.

    i.e

    The possible reason that you want to add another argument.

    If the rule to test the value(string), it will be possibly totally different from the code that I have already posted. I mean another method to use.

    What I understood at the first time is to test the whole string with others as a part.
    Then it is now part and part. ??

    rgds,
    jindon

  20. #20
    Registered User
    Join Date
    05-30-2005
    Posts
    19
    At present this is my situation;

    I am trying to compare cells (alpha-numeric entries) in 2 columns, A and C (of different depths). Some of the cell entries in A and C are exactly equal, while the majority are not.

    If a cell in C [say C1] matches a cell in A [say A1], a cell in E [E1] displays ".... Found". Otherwise, the next closest match is returned.

    The following is the UDF;
    **************************************************
    * UDF code by Jindon *
    **************************************************
    Function wfindx(r As Range, rng As Range, Optional cap As Integer) As String

    Dim txt1, txt2, rngArray, x, flag As Boolean
    ' If cell is empty; EXIT!
    If IsEmpty(r) Then Exit Function

    txt1 = Split(r, " ") ' Split r value by space and put them into an array.
    rngArray = rng.Value ' Put the rng values in an array.

    If IsMissing(cap) Then ' Adjusting arg, cap.
    cap = UBound(txt1) ' If missing; cap = ubound(txt1).

    ' If cap is bigger than ubound(txt1).
    ElseIf cap - 1 > UBound(txt1) Then
    cap = UBound(txt1)
    Else
    cap = cap - 1 ' Because Lower bound of the array populated from
    End If ' Split function is always 0, so needs to be adjusted

    For i = LBound(txt1) To cap
    x = x & txt1(i) & Chr(32) ' Populate testing string according to the cap value
    Next
    ' Put the testing string to variable x
    x = Trim(x)
    For i = LBound(rngArray) To UBound(rngArray) ' Start loop to test rngArray
    ' If length of rngArray value is less than testing value, go to Next
    If Len(rngArray(i, 1)) < Len(x) Then GoTo skip
    ' If the testing value found from the begining of the rngArray value
    If InStr(1, rngArray(i, 1), x, vbTextCompare) = 1 Then
    wfindx = r & " Found": Exit Function
    End If
    skip:
    Next

    End Function
    ***********************************************

    Problem with this UDF;
    Col A:
    D11 10 9 8 TRI STATE LKG 0V
    D11 10 9 8 TRI STATE LKG 0V
    D11 10 9 8 TRI STATE LKG 0V
    D11 10 9 8 TRI STATE LKG 0V

    Col C;
    D11 10 9 8 TRI STATE LKG 0V
    D11 10 9 8 TRI STATE LKG 1V
    D11 10 9 8 TRI STATE LKG 2V
    D11 10 9 8 TRI STATE LKG 3V

    and type into col E: "=wfindx(Cell,Range,3)"
    then Col E returns;
    D11 10 9 8 TRI STATE LKG 0V Found
    D11 10 9 8 TRI STATE LKG 1V Found
    D11 10 9 8 TRI STATE LKG 2V Found
    D11 10 9 8 TRI STATE LKG 3V Found


    even though :-
    D11 10 9 8 TRI STATE LKG 1V Found
    D11 10 9 8 TRI STATE LKG 2V Found
    D11 10 9 8 TRI STATE LKG 3V Found
    -: are not in the range (Col A).

    Can you advise me how to fix this.?

    OR:-

    could I avoid the hassle of a lengthy UDF by using a VB function?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1