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


--
PLPE
------------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...o&userid=23856
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

What should be the result for the example you provided. Should it be no
found. If otherwise, on what parameters do you want to match the
texts?

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

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

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" wrote in message
...

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

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" wrote in message
...

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

comparing 2 columns
 

Hi,

UDF

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


Code
-------------------

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

-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

Column A & B do not match exactly, but are pretty close.
Originally, both columns had '_' & '@' included, but I added macros t
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 workin
for me!

--
PLP
-----------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

jindon Wrote:


Code:
--------------------

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


--
PLPE
------------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...o&userid=23856
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

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

"PLPE" wrote in message
...

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

comparing 2 columns
 

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

Code
-------------------

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

-------------------


PLPE Wrote:
Column A & B do not match exactly, but are pretty close.
Originally, both columns had '_' & '@' included, but I added macros t
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 workin
for me!

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

Hi PLPE,

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

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

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 yo

--
PLP
-----------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

jindon Wrote:
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

Code:
--------------------

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


--
PLPE
------------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...o&userid=23856
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

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)

comparing 2 columns
 

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.


--
PLPE
------------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...o&userid=23856
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

how much more complicated would this UDF get if I were to add a 4t
argument??? :confused

--
PLP
-----------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

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


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=375010

comparing 2 columns
 

what's a CLEAR LOGIC

--
PLP
-----------------------------------------------------------------------
PLPE's Profile: http://www.excelforum.com/member.php...fo&userid=2385
View this thread: http://www.excelforum.com/showthread.php?threadid=37501

comparing 2 columns
 

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


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=375010