Need help with lookup on text format
 

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.

Let's say A13 contains "John, Smith" and B13 contains "Smith, John".

C13 = IF(AND(LEFT(A13,FIND(", ",A13)-1)=RIGHT(B13,LEN(B13)-FIND(",
",B13)-1)),"Match","No match")



Krista Wrote:
I have two worksheets with name fields, however the name fields are
reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in
worksheet
2.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=465695

Let's say A13 contains "John, Smith" and B13 contains "Smith, John".

C13 = IF(AND(LEFT(A14,FIND(", ",A14)-1)=RIGHT(B14,LEN(B14)-FIND(",
",B14)-1),LEFT(B14,FIND(", ",B14)-1)=RIGHT(A14,LEN(A14)-FIND(",
",A14)-1)),"Match","No match")


Krista Wrote:
I have two worksheets with name fields, however the name fields are
reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in
worksheet
2.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=465695

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.

The name format:

John Smith worksheet 1 & Smith,John in worksheet 2

"B. R.Ramachandran" wrote:

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.

Thanks, however the names are listed as follows:

John Smith (worksheet 1) & Smith,John (worksheet 2)

I believe there is away for me to seperate the name from worksheet 2 then I
could put the name back together in the order I need it?

"Morrigan" wrote:


Let's say A13 contains "John, Smith" and B13 contains "Smith, John".

C13 = IF(AND(LEFT(A13,FIND(", ",A13)-1)=RIGHT(B13,LEN(B13)-FIND(",
",B13)-1)),"Match","No match")



Krista Wrote:
I have two worksheets with name fields, however the name fields are
reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in
worksheet
2.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=465695

Could I break the name from worksheet 2 apart (Smith, John) to show John in
one cell and Smith in another cell, then I could put them back together as
John Smith?

Thanks for your help.

"B. R.Ramachandran" wrote:

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.

Hi,
If the name format is John Smith in Worksheet 1 and Smith,John in Worksheet
2 (i.e., no space after the comma), try the following formula (a modification
of the one in my previous reply)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7))&"
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

It, I believe, would work.

Regards,
B. R. Ramachandran

"Krista" wrote:

The name format:

John Smith worksheet 1 & Smith,John in worksheet 2

"B. R.Ramachandran" wrote:

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.

Hi,

Use the following formula in B2 (in Worksheet 2) and drag it down the column
B,

=RIGHT(Sheet2!$A2,LEN(Sheet2!$A2)-FIND(",",Sheet2!$A2))&"
"&LEFT(Sheet2!$A2,FIND(",",Sheet2!$A2)-1)

It will flip Smith,John to John Smith

"Krista" wrote:

Could I break the name from worksheet 2 apart (Smith, John) to show John in
one cell and Smith in another cell, then I could put them back together as
John Smith?

Thanks for your help.

"B. R.Ramachandran" wrote:

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.