Hi

I currently have 2 reports ... Both have peoples names in it. I would
like to compare the names in one report to the names in the other
report. If the names are the same I want the report to state ok, if
not it needs to state WRONG

Workbook 1

Column A Last Name
Colum B First Name
Column C Location
Column D - OK? WRONG? Is the last name in WB1 = the last name in WB2

Worksbook 2

Column A Last Name
Colum B First Name
Column C Location

PLEASE HELP ME .......:(


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961

the easiest way is just to use the inbuilt test. To illustrate

In A1 enter Matt
In B1 enter Mat
in C1 enter =A1=B1
C1 will read false. If you change B1 to Matt, C1 will change to true


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=560961

A simple IF comparison is only meaningful if you expect both lists to be
identical in length, order and content: this effectively traps "typing"
errors. If the lists are different lengths, then you will have to use another
approach and probably compare the combined last name/first name of both
workbooks to allow duplicate surnames.

One way is to create a "helper" column in each workbook (e.g column E)
containing the concatenation of last name & firstname, and then use (for
example) in WB1 in Col F:

=IF(COUNTIF([WB2.xls]Sheet1!$E:$E,E1),"OK","Wrong")

HTH

"Karen271077" wrote:


Hi

I currently have 2 reports ... Both have peoples names in it. I would
like to compare the names in one report to the names in the other
report. If the names are the same I want the report to state ok, if
not it needs to state WRONG

Workbook 1

Column A Last Name
Colum B First Name
Column C Location
Column D - OK? WRONG? Is the last name in WB1 = the last name in WB2

Worksbook 2

Column A Last Name
Colum B First Name
Column C Location

PLEASE HELP ME .......:(


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961

Hi Toppers,

You are indeed correct, both lists are different in size. Again each
employee has 2 records, for the purpose of data inteegrety I need both
records to reflect exactly the same name. Lets just work with one
column which is their last name. List 1 might say Cordova as a last
name and list 2 might say cordova salinas. Whenever i have an issue
like this my report needs to state false. I tried your formula but
can't seem to work it out (is the condition case sensitive by any
chance?


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961

It is not case sensitive and will return FALSE if comparing "Cordova" vs
"Cordova Salinas" irrespective of case of either cell.

So I don't understand why it is not working for you.

"Karen271077" wrote:


Hi Toppers,

You are indeed correct, both lists are different in size. Again each
employee has 2 records, for the purpose of data inteegrety I need both
records to reflect exactly the same name. Lets just work with one
column which is their last name. List 1 might say Cordova as a last
name and list 2 might say cordova salinas. Whenever i have an issue
like this my report needs to state false. I tried your formula but
can't seem to work it out (is the condition case sensitive by any
chance?


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961

Hi toppers ... now it's working :)
Now I want to take it one step further ...

So now I have compared

WS 1 Column A - Cordova
WS 2 Column A - Cordova Salinas - Result is displayed as WS1 Column B
Wrong

Now I would like to display the name cordova Salinas in WS1 Column C so
that my people know the diffrenece


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961

=IF(B1="Wrong",VLOOKUP("*"&A1&"*",ws2!A:A,1,0),"")

Or combine them in B1

=IF(COUNT(ws2!A:A,A1),"",VLOOKUP("*"&A1&"*",ws2!A: A,1,0))

This assumes that "Part" of the name exist i.e if "Cordova" is not in any
part of the list, it will error so use:

in C1

=IF(B1="Wrong",IF(ISNA(VLOOKUP("*"&A1&"*",ws2!A:A, 1,0)),"No
match",VLOOKUP("*"&A1&"*",ws2!A:A,1,0)))

OR

in B1

=IF(COUNT(Sheet2!A:A,!A1),"",IF(ISNA(VLOOKUP("*"&A 1&"*",ws2!A:A,1,0)),"No
match",VLOOKUP("*"&A1&"*",ws2!A:A,1,0)))

HTH

"Karen271077" wrote:


Hi toppers ... now it's working :)
Now I want to take it one step further ...

So now I have compared

WS 1 Column A - Cordova
WS 2 Column A - Cordova Salinas - Result is displayed as WS1 Column B
Wrong

Now I would like to display the name cordova Salinas in WS1 Column C so
that my people know the diffrenece


--
Karen271077
------------------------------------------------------------------------
Karen271077's Profile: http://www.excelforum.com/member.php...o&userid=36195
View this thread: http://www.excelforum.com/showthread...hreadid=560961