comparing 2 columns

**PLPE** · 05-30-2005, 09:36 AM

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 · 05-30-2005, 01:05 PM

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** · 05-30-2005, 05:11 PM

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

**mangesh_yadav** · 05-30-2005, 11:42 PM

Hi PLPE,

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

Mangesh

**PLPE** · 05-31-2005, 10:33 AM

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

**PLPE** · 06-02-2005, 04:45 AM

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

**jindon** · 06-02-2005, 05:52 AM

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

**PLPE** · 06-02-2005, 06:44 AM

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.