Modify a vLookup?
 

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

Here is a tiny trick to help reduce the pressure on you. Suppose column A
contains data like:

J Ravenswood
Ravenswood J

In C1 enter:
=IF(FIND(" ",A1,1)=2,A1,RIGHT(A1,1) & " " & LEFT(A1,LEN(A1)-2))
and copy down. This will convert all the names into
<initial<space<lastname

Then use any VLOOKUP() on columns B & C


--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

not sure exactly what ya want,

put this in col C to recon names to a standard format

=PROPER(B1&" "&REPLACE(A1,FIND(B1,A1),LEN(B1),""))

"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

Thanks GS, that works if there are spaces, but no spaces. Got a modification
for no spaces?

Thanks!
-Max

"Gary''s Student" wrote:

Here is a tiny trick to help reduce the pressure on you. Suppose column A
contains data like:

J Ravenswood
Ravenswood J

In C1 enter:
=IF(FIND(" ",A1,1)=2,A1,RIGHT(A1,1) & " " & LEFT(A1,LEN(A1)-2))
and copy down. This will convert all the names into
<initial<space<lastname

Then use any VLOOKUP() on columns B & C


--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

Hi Rich! That didn't do anything at all :P.

I have mixed up data in 2 different formats, (first initial,last name) and
(last name,first initial) no spaces.
I need to reconcile the list by last name (Column A has the mixed up format,
column B has the last names of the same users in Column A).

Thanks!
-C


"Rich" wrote:

not sure exactly what ya want,

put this in col C to recon names to a standard format

=PROPER(B1&" "&REPLACE(A1,FIND(B1,A1),LEN(B1),""))

"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

If there are no spaces, we will have to check to see if the last letter is
capitalized or lower case. Check back tomorrow.
--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Thanks GS, that works if there are spaces, but no spaces. Got a modification
for no spaces?

Thanks!
-Max

"Gary''s Student" wrote:

Here is a tiny trick to help reduce the pressure on you. Suppose column A
contains data like:

J Ravenswood
Ravenswood J

In C1 enter:
=IF(FIND(" ",A1,1)=2,A1,RIGHT(A1,1) & " " & LEFT(A1,LEN(A1)-2))
and copy down. This will convert all the names into
<initial<space<lastname

Then use any VLOOKUP() on columns B & C


--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max

Modify a vLookup?
 

No caps, usernames are all lower case.

Thx!

"Gary''s Student" wrote:

If there are no spaces, we will have to check to see if the last letter is
capitalized or lower case. Check back tomorrow.
--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Thanks GS, that works if there are spaces, but no spaces. Got a modification
for no spaces?

Thanks!
-Max

"Gary''s Student" wrote:

Here is a tiny trick to help reduce the pressure on you. Suppose column A
contains data like:

J Ravenswood
Ravenswood J

In C1 enter:
=IF(FIND(" ",A1,1)=2,A1,RIGHT(A1,1) & " " & LEFT(A1,LEN(A1)-2))
and copy down. This will convert all the names into
<initial<space<lastname

Then use any VLOOKUP() on columns B & C


--
Gary''s Student - gsnu200777


"Max Pressure" wrote:

Hi,
Here's what I've got:
Column A has 400 usernames in 2 different formats:
<lastname<firstinitial and <firstinitial<lastname
(and yeah, they're all mixed up)

Column B Has all these users listed by last name

I'm trying to reconcile Column B against Column A. I have some experience
with the vLookup function but I don't know how to make this scenario work.

Any assistance would be greatly appreciated!
-Max