Vlookup problems

[Letto4135]

I am making a spreadsheet that automatically alphabetized as data is input by last name then first name for those that have the same last name, problem is I can't figure out how compare the two.

I have helper columns that trim to last name only, and countif <= converts last name into numbers that are numerically the order the last name should be in order to alphabetize.

After this the vlookup I use finds #1 and inputs that name.

Problem I have is if there are two last names that are the same it skips a number and then will have two of the next number, ex. skips 10 then two 11s. This creates a problem for the vlookup when there is no #10 it leaves the space blank.

 

[BarryL]

can you give a sample table of your data?

 

[Letto4135]

can you give a sample table of your data?

I'm not at work anymore but I can attempt to recreate some of it.

Original input
Debra Smith
Chris Faro
Ted Blankenship
Bill Blankenship

The first helper (trim right repl) colum makes it
Smith
Faro
Blankenship
Blankenship

Second helper (countif <=) makes it
4
3
2
2

Vlookup looks for the number and inputs the original input like this.
1 Blank space
2 Ted Blankenship
3 Chris faro
4 Debra smith

If I could tell it to look at last name then see if there is a duplicate then compare first names of the duplicates to alphabetize between them it would work but I can't find a way.

 

[Letto4135]

Other than that I'll be back at work tonight and can give more then

 

[Letto4135]

Imgur: The most awesome images on the Internet This is where the names get trimmed to last name only

Imgur: The most awesome images on the Internet This "counts" the names in alphabetical order but makes the zeros blank spaces so I added;

Imgur: The most awesome images on the Internet This to subtract the zero spots to make them negative

Last step Imgur: The most awesome images on the Internet

That blank in the first spot is my problem. Can't get it to work, I'm not sure where I'm even stuck.


The formulas in the order of the pictures are

=TRIM(RIGHT(SUBSTITUTE(E2," ",REPT(" ",100)),100))

=COUNTIF($D$2:$D$17,"<="&D2)

=COUNTIF($D$2:$D$18,"<="&D2)-COUNTIF(C2:C17,"0")

=IFERROR(VLOOKUP(1,$B$2:$E$17,4,FALSE),"")

 

[Letto4135]

Is this solvable?