comparing 2 columns

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

Originally Posted by jindon

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

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
>

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

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
>

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

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

Hi PLPE,

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

Mangesh

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

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

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

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

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

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
' ****  put the testing string into array txt1 by separating by space ***
        If IsMissing(cap) Then
' ****  if cap, 3rd argument, is missing  ****
            cap = UBound(txt1)
' ****   set cap = upperbound of txt1, which means the number of words
'        separated by the spaces  *****
        Else
            cap = cap - 1
        End If
        For Each c In rng
            txt2 = Split(c, " ")
' ****   put the string into array txt2 by separating by space ****
                For i = LBound(txt1) To cap
                    If Trim(txt1(i)) Like Trim(txt2(i)) & "*" Then
' **** if ith string in txt1 array is Like ith string in txt2 array then set flag=true**
                        flag = True
                    Else
' **** if ith string of txt1 array and txt2 array differ then set flag=false and exit loop***
                        flag = False: Exit For
                    End If
                    If i = UBound(txt2) Then Exit For
                Next
' **** after loop, if flag is still true, returns "Found" and exit function ***
            If flag = True Then
                wfind = r & " Found": Exit Function
            End If
        Next
' **** else, returns "not found" and end function ****
        wfind = r & " not found"
        
End Function

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

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.