Find-Replace Names

[pskwaak]

I have column A (below) of names originally entered with last name only (and first name initial if duplicate last name); I now have a list (column B below) with the first and last names of same people. I want to replace column A with column B...without having to manually re-enter each name. Lists contain over 600 names

COLUMN A COLUMN B
Bender Alan Miller
Jones Brent Jones
MillerA Jordy Bender
MillerJ John Miller

After solving the above, I then need to find-replace the "new" Column A names in another spreadsheet which contains multiple entries for each person listed in the "old" column A format. Will the answer to the above work on this spreadsheet as well?

[misrasomendra]

Not Quite Sure this will meet your requirement, but with values in Column A, you cannot replace them then and there with formulas, what you can do bring the names in say column C and than do copy - Paste Special - Value in column A. With above data in column A & B try below formula in Column C1 and copy down:

Please Login or Register  to view this content.

Note this is an array formula so must be entered with Ctrl+Shift+Enter.

This will not deal with duplicate First name or names with first letter of First name as double say MillerA and there is Alan Miller & Andy Miller than this will fail. See you data.

EDIT: Now it will encouter Alan Miller & Andy Miller.

[pskwaak]

Formula didn't work--resulted in NUM! error. All columns are text, no numerical values.

Appreciate the help, any other ideas?

[hoyasaxa215]

Can you upload a sample file? The columns aren't quite clear when typed out.

[pskwaak]

NameList.xlsm

Here's a portion of the list.

[hoyasaxa215]

And is it safe to assume that there are no people with the same last name?

[hoyasaxa215]

Well, there's only one match on the list you provided. But I split the first and last names into two separate cells using the Text to Columns feature and then just performed a =VLOOKUP on the last name and returned the full name. Presumably when you update this to the full list, you won't receive the errors. Equation is highlighted in green.

[AlKey]

Try this one

=IFERROR(IFERROR(VLOOKUP("*"&A1&"*",$B$1:$B$18,1,0),VLOOKUP("*"&RIGHT(A1)&" "&"*"&LEFT(A1,LEN(A1)-1),$B$1:$B$18,1,0)),"")

	A	B	C
1	Adams	Addie Joss	Babe Adams
2	Alexander	Al Benton
3	Allen	Al Boehling	Allen Sothoron
4	Ames	Al Brazle
5	Andrews	Al Demaree
6	Antonelli	Al Mamaux
7	Ayers	Allen Sothoron
8	Bagby	Allie Reynolds
9	Barnes	Alvin Crowder
10	Barrett	Andy Karl
11	Beggs	Art Houtteman
12	Bender	Art Nehf
13	BentonA	Babe Adams
14	BentonL	Barney Pelty	Al Benton
15	BentonR	Bill Dineen
16	Benz	Bill Doak
17	Bickford	Bill Donovan
18	Blackwell	Bill Hallahan

[pskwaak]

Yes, there are, but the last name carries the letter of first name, i.e., BentonA, BentonL. There's about 300 total names on the list, with a few duplicates like this.

[pskwaak]

That looks good. The only tweak I see is on the duplicate names (BentonA and BentonL) but that may correct itself with the full listing. I'll run it and see.

Thanks much!

[pskwaak]

Thanks! That was the sequence I was striving for, but didn't know how to split the name into columns. Is that a formula or part of a copy-paste option?

[pskwaak]

The formula did not work for duplicate names, but did perfect for all others ... thanks again! Not sure what it would take to correct the duplicates without another long string of formula...but this worked well enough for me.

[misrasomendra]

Hi pskwaak,

I think you did not read my comment, it clearly stated that the formula is an array formula and must be entered with Ctrl+Shift+Enter. With the file you uploaded there were three names that came out Babe Adams, Allen Sothoron, Al Benton. See the file, I did not change anything in the formula just extend the range. The error can be replaced by a blank cell if you wrap the formula inside IFERROR function.