can I use IF function to compare 2 columns

[Karen271077]

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

[Mallycat]

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

[Toppers]

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

[Karen271077]

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?

[Toppers]

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

[Karen271077]

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

[Toppers]

=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("*"&A1&"*",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
>
>