Alphabetizing/VLookup Problem

[BenjieLop]

I need help in determining a formula for this problem please.

These are my data:

LNAME FNAME CODE
Smith John 123
Samuels Billy 456
Ashby Hank 789
Aaron Kalvin 234
Smith Elaine 345
Johnson Kathy 890
Custer Blake 543

After entering the above data, I would like the final columns t
AUTOMATICALLY appear (e.g., in Sheet 2) as shown below:

LNAME FNAME CODE
Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith Elaine 345
Smith John 123

PLEASE NOTE:

1. The final LNAME column is ALPHABETIZED;
2. The final FNAME column contains their corresponding first
names;
3. The final CODE column contains their corresponding codes

Any help is very much appreciated. Thank you and regards

 

[Frank Kabel]

Hi
using an approach Harlan Grove posted some time ago you may use the
following array formulas (all entered with CTRL+SHIFT+ENTER) on your
second sheet. I assume that you have entered the heading row in row 1
of both sheets:

A2:
=INDEX(sheet1!$A$2:$A$100,MATCH(0,COUNTIF(sheet1!$A$2:$A$100,"<"&sheet1
!$A$2:$A$100),0))

A3:
=IF(COUNTIF(sheet1!$A$2:$A$100,A2)>COUNTIF(A$2:A2,A2),A2,INDEX(sheet1!$
A$2:$A$100,MATCH(ROW()-ROW(sheet1!$A$2),COUNTIF(sheet1!$A$2:$A$100,"<"&
sheet1!$A$2:$A$100),0)))

B2:
=INDEX(sheet1!$B$2:$B$100,MATCH(0,COUNTIF(sheet1!$A$2:$A$100,"<"&sheet1
!$A$2:$A$100),0))

B3:
=IF(COUNTIF(sheet1!$A$2:$A$100,A2)>COUNTIF(A$2:A2,A2),INDEX(sheet1!$B$2
:$B$100,SMALL(IF(sheet1!$A$2:$A$100=A3,ROW(sheet1!$A$2:$A$100)),COUNTIF
(A$2:A3,A3))-1),INDEX(sheet1!$B$2:$B$100,MATCH(ROW()-ROW(sheet1!$A$2),C
OUNTIF(sheet1!$A$2:$A$100,"<"&sheet1!$A$2:$A$100),0)))

C2 / C3 adapt B2/B3 accordingly

Copy row 3 down as far as needed

 

[BenjieLop]

Hello Frank,

Thank you for your formulas. I could not, in my wildest dreams, come u
with what you have done.

There is just one small hitch though and this is the part that has bee
stumping me for a long time.

Please take note of the differences (last 2 entries) in the followin
two tables. This first table is what I would like to achieve.

LNAME FNAME CODE
Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith Elaine 345
Smith John 123

Using your formulas, this is the table that came out

LNAME FNAME CODE
Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith John 123
Smith John #N/A

PLEASE NOTE the last two entries whose last names are both "Smith" an
this is where I have been getting stuck. I have not had any succes
whenever two persons have the same last names.

Is there a way of going about this?

Again, thank you for what you have done. You are one excellent Excele
... more power to you.






*********************************************
LNAME FNAME CODE

 

[BenjieLop]

Michael,

Thank you for your input. This VLookup formula ONLY works when all th
last names in a column are unique. When there are two entries of th
same name (in this case, "Smith"), this Vlookup formula reads only th
data associated with the first "Smith" in the column.





**********************************************

 

[Frank Kabel]

Hi
just tested my formulas on your example and I got the following list
Aaron Kalvin 234

Ashby Hank 789

Custer Blake 543

Johnson Kathy 890

Samuels Billy 456

Smith Elaine 345

Smith John 123



so what formulas have you used in column B and column C. Note: these
formulas are different thant the formulas in column A (just to deal
with this kind of duplicate names)

 

[BenjieLop]

Hi Frank,

Thank you for testing it further and it is so nice to know that you go
the results that I have been trying to achieve.

I will take it easy for the rest of the day and will get back t
analyzing (and copying) the formulas that you sent when my brains ar
rested. I will definitely let you know the results tomorrow.

Again, thanks a lot and more power to you

 

[BenjieLop]

Hello Frank,

I copied your formulas "to the letter" (so to speak) and here is what
came up with. I am pasting yours here so you can see what came out o
my efforts, as well.

YOURS

Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith Elaine 345
Smith John 123

MINE

Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith John 123
Smith Elaine 345

As you can see, the last two entries in YOURS and MINE ar
interchanged. HOWEVER, I am no longer worried about this... I am jus
happy that I have found a formula that is able to deal with duplicat
last names.

Again, thank you and more power to you.


<< Re: Alphabetizing/VLookup Problem

Hi
just tested my formulas on your example and I got the following list

Aaron Kalvin 234
Ashby Hank 789
Custer Blake 543
Johnson Kathy 890
Samuels Billy 456
Smith Elaine 345
Smith John 123


so what formulas have you used in column B and column C. Note: thes
formulas are different thant the formulas in column A (just to dea
with this kind of duplicate names) >