comparing 2 columns

[PLPE]

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

[mangesh_yadav]

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

[PLPE]

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")'.

[jindon]

Hi,

UDF

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

Function wfind(r As Range, rng As Range) As String

Dim c As Range, txt1, txt2, flag As Boolean
        txt1 = Split(r, " "): flag = False
        For Each c In rng
            txt2 = Split(c, " ")
                For i = LBound(txt1) To UBound(txt1)
                    If Trim(txt1(i)) Like Trim(txt2(i)) & "*" Then
                        flag = True
                    Else
                        flag = False
                    End If
                Next
            If flag = True Then
                wfind = r & " Found": Exit Function
            End If
        Next
        wfind = r & " not found"
        
End Function

[PLPE]

Function wfind(r As Range, rng As Range) As String

Dim c As Range, txt1, txt2, flag As Boolean
        txt1 = Split(r, " "): flag = False
        For Each c In rng
            txt2 = Split(c, " ")
                For i = LBound(txt1) To UBound(txt1)
                    If Trim(txt1(i)) Like Trim(txt2(i)) & "*" Then
                        flag = True
                    Else
                        flag = False
                    End If
                Next
            If flag = True Then
                wfind = r & " Found": Exit Function
            End If
        Next
        wfind = r & " not found"
        
End Function

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? ♦¿♦

[Nigel]

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
>

[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!}

[Jim May]

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
>

[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

Function wfind(r As Range, rng As Range, Optional cap As Integer) As String

Dim c As Range, txt1, txt2, flag As Boolean, i As Integer
        txt1 = Split(r, " "): flag = False
        If IsMissing(cap) Then
            cap = UBound(txt1)
        Else
            cap = cap - 1
        End If
        For Each c In rng
            txt2 = Split(c, " ")
                For i = LBound(txt1) To cap
                    If Trim(txt1(i)) Like Trim(txt2(i)) & "*" Then
                        flag = True
                    Else
                        flag = False: Exit For
                    End If
                    If i = UBound(txt2) Then Exit For
                Next
            If flag = True Then
                wfind = r & " Found": Exit Function
            End If
        Next

        wfind = r & " not found"
        
End Function

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!}

[Nigel]

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
>