Equation to output 'agent name' "Copy" or "Not Found"

[oprik]

Hello,
I had an excel test that I didn't do particularly well on, but this one question had me stumped.
		I need an equation that takes the name in this list and out puts the agent name in 'first.initial' or "COPY" or "NOT FOUND"
Sheet 1:		Convert these names:	Correct output:
Agent		David Everit		David.e
		Joe a.		Joe.A
Adam.m		Ben		Ben.B
		Sam p.		NOT FOUND
Agent		Training 22		Training.22
		David E.		COPY
David.e		Kat Cathy		Kat.C
		David D.		NOT FOUND
Agent		Adam.S		NOT FOUND
Joe.A
Agent
Ben.B
Agent
Kat.C
Agent
Training.22
Agent
Admin
Agent
Training.22
The equation i submitted that my teacher said was wrong was:

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

=IFNA(IF(COUNTIF(Sheet1!A:A, A3)>1, "Duplicate", INDEX(Sheet1!A:A,MATCH(A3&"*",Sheet1!A:A,0))),"Not Found")[/QUOTE]

 

[baitmaster]

Your main problem is that you were required to convert the name into a standard format before doing the analysis. Assuming this data is pasted in columns A and B of the same worksheet you'd start with something like [=LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1)] to get the name. Then you'd need to check if that same name had been found already, then wrap the whole thing in an error handler to get the "not found". Easier in multiple steps, but to do it all in one formula is a bit nasty and unnecessary!

 

[oprik]

Your main problem is that you were required to convert the name into a standard format before doing the analysis. Assuming this data is pasted in columns A and B of the same worksheet you'd start with something like [=LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1)] to get the name. Then you'd need to check if that same name had been found already, then wrap the whole thing in an error handler to get the "not found". Easier in multiple steps, but to do it all in one formula is a bit nasty and unnecessary!

I thought about doing that but that equation only works if you are given both the first and last name of the agent, so in the example it would work for David Everit to make David.E but not for Ben. I also found that the equation INDEX(Sheet1!A:A,MATCH(LEFT(A9,5)&"*",Sheet1!A:A,0)) worked for some of the Agents but not all. Nasty and unnecessary is what i thought too, I could think of a way to do this in three columns but not all in one

 

[baitmaster]

In C2, copied down:
=IF(ISERROR(LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1)),"not found",IF(COUNTIF($C$1:C1,LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1))>0,"duplicated",LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1)))

This is an awkward formula to read but was constructed in 3 separate columns and then merged together, which made it easier to create. My 3 construction formulas were E2:G2 as follows:
=LEFT(B2,FIND(" ",B2)-1)&"."&MID(B2,FIND(" ",B2)+1,1)
=COUNTIF($E$1:E1,E2)>0
=IF(ISERROR(E2),"not found",IF(COUNTIF($E$1:E1,E2)>0,"duplicated",E2))


Being pedantic, Ben should fail because there's not enough info to create first.initial

 

[baitmaster]

And it turns out I got it wrong too, as I forgot to check the name against the original list using MATCH and confirm it was actually there. Oh well, you get the gist...

 

[oprik]

Thank you, You've given me some great ideas on how to do this, but you keep referencing the 'Convert these names:' list as apposed to searching the 'Sheet1'. I think what i'll do is some kind of index/match function using your Left( ,FIND()) & MID( ,FIND)) combination to see if i can get the equation to also search for single named agents like Ben to have it comeback with Ben.B

 

[oprik]

And it turns out I got it wrong too, as I forgot to check the name against the original list using MATCH and confirm it was actually there. Oh well, you get the gist...

The correct answer the Teacher was looking for was:
=IF(SUMPRODUCT(LEN(Sheet1!A:A)-LEN(SUBSTITUTE(LOWER(CALLER!A:A),LOWER('Sheet2'!A3),"")))/LEN('Sheet2'!A3)>1,"COPY",IFERROR(INDEX(Sheet1!A:A,MATCH(IFERROR(SUBSTITUTE(LEFT(A3,FIND(" ",A3,1)-1)&"."&MID(A3,FIND(" ",A3,1),2)," ",""),A3)&"*",Sheet1!A:A,0)),"Not Found"))