Formula for Alphabetizing

[cbrooks]

I need to create a formula (that will eventually be a macro) to alphabetize a column of names.

However, I only need to alphabetize the last names and after importing data off the web, I have both first and last names. Ex:

Joe Davis
Shawn Horne
Eric Lofgren
Steve Skrinar
Jason Lewis
David Burne

I need to write a formula that will skip or drop the first names and alphabetize like this:
Burne
Davis
Horne
Lewis
Lofgren
Skrinar

these names and data imported from the web will be in a worksheet that I need to draw certain names and corresponding data into another worksheet. I am thinking of having a formula in the final worksheet that will find the appropriate name and pull the data for that person.

So in essence, I need a formula for alphabetizing and also for matching names linked to a second worksheet.

THANKS VERY MUCH FOR YOUR HELP!

[Richard Buttrey]

Hi,

To extract the last name from a cell, (I'm assuming not all names will be a simple first/last name) use:

Please Login or Register  to view this content.

then just sort on this column as usual.
HTH

[cbrooks]

thanks for replying! I am still a little unsure which cell I write the formula in? I will need to keep this as a macro once I find out it works to constantly do alphabetize by last name after importing from the web.

Currently in cell A3:A27 I have a list of names like this:
Elvis Andrus
Joaquin Arias
Hank Blalock
Brandon Boggs
Julio Borbon
Marlon Byrd
Nelson Cruz
Chris Davis
Scott Feldman
Ryan Freel
Craig Gentry
Esteban German
Greg Golson
Josh Hamilton
Rich Harden
Matt Harrison
Derek Holland
Joe Inglett
Jason Jennings
Ian Kinsler
Brandon McCarthy
David Murphy
Kevin Richardson
Jarrod Saltalamacchia
Taylor Teagarden

I need to drop all first names and sort by last name. Or at least, just sort by last name. Is there a way to highlight the range and automate for the whole range or do I have to write the formula for each individual cell?

thanks so much for your help!

[cbrooks]

I guess what I really need to do, since I can sort using the sort key, is just drop the first name and keep the last name in each cell.

[Richard Buttrey]

No,

Just put the formula I gave you in B3 (and change the formula A1 reference to A3 since I used the standard convention hereabouts for an example formula reference), then copy B3 down to B27. This will give you all the last names. Then just sort them.

HTH

[cbrooks]

thanks! that works after adding a new blank column in B.

is there a way to not have to add a new column and keep the names in their original cells A3:A27?

[Richard Buttrey]

Hi,

Not quite sure what you mean. You should leave the original names in A3:A27, use the formula in B3:B27 then sort the whole of A3:B27 using column B as the primary sort.

Rgds

[cbrooks]

this is what my worksheet looks like...
Elvis Andrus Andrus Tex 2045 418 1213 832 116 59.3 20.4 50.2 13.9 540 136 25.2
Joaquin Arias Arias Tex 28 6 13 15 5 46.4 21.4 40 33.3 9 2 22.2
Hank Blalock Blalock Tex 1830 358 950 880 212 51.9 19.6 40.7 24.1 495 134 27.1
Brandon Boggs Boggs Tex 70 9 44 26 8 62.9 12.9 34.6 30.8 18 3 16.7
Julio Borbon Borbon Tex 682 135 392 290 42 57.5 19.8 46.6 14.5 179 37 20.7
Marlon Byrd Byrd Tex 2148 459 1052 1096 232 49 21.4 41.9 21.2 599 221 36.9
Nelson Cruz Cruz Tex 2002 346 1044 958 316 52.1 17.3 36.1 33 515 195 37.9
Chris Davis Davis Tex 1632 243 769 863 335 47.1 14.9 28.2 38.8 419 162 38.7
Scott Feldman Feldman Tex 23 7 10 13 1 43.5 30.4 53.8 7.7 8 3 37.5
Ryan Freel Freel KC(3)* 414 67 232 182 35 56 16.2 36.8 19.2 103 22 21.4
Craig Gentry Gentry Tex 79 12 45 34 8 57 15.2 35.3 23.5 19 2 10.5
Esteban German German Tex 200 39 122 78 9 61 19.5 50 11.5 50 9 18
Greg Golson Golson Tex 4 0 3 1 0 75 0 0 0 1 0 0
Josh Hamilton Hamilton Tex 1336 261 580 756 220 43.4 19.5 34.5 29.1 365 181 49.6
Rich Harden Harden ChC 170 32 63 107 35 37.1 18.8 29.9 32.7 48 23 47.9
Matt Harrison Harrison Tex 11 0 7 4 1 63.6 0 0 25 2 1 50
Joe Inglett Inglett Tor 321 69 170 151 25 53 21.5 45.7 16.6 99 39 39.4
Jason Jennings Jennings Tex 2 1 1 1 0 50 50 100 0 1 0 0
Ian Kinsler Kinsler Tex 2455 498 1326 1129 143 54 20.3 44.1 12.7 640 170 26.6
Brandon McCarthy McCarthy Tex 16 4 7 9 4 43.8 25 44.4 44.4 4 1 25
David Murphy Murphy Tex 2057 337 1221 836 165 59.4 16.4 40.3 19.7 494 86 17.4
Kevin Richardson Richardson Tex 25 4 13 12 3 52 16 33.3 25 6 1 16.7
Jarrod Saltalamacchia Saltalamacchia Tex 1179 190 556 623 182 47.2 16.1 30.5 29.2 309 125 40.5
Taylor Teagarden Teagarden Tex 918 127 486 432 166 52.9 13.8 29.4 38.4 218 62 28.4
Michael Young Young Tex 2188 455 1174 1014 203 53.7 20.8 44.9 20 593 180 30.4
Player Team #Pit In Taken Swing Miss %Pit %Put %Swg %Swg 1st 1stPit %Swg
Play Taken InPlay InPlay Miss Pit Swg 1stPit


i don't have a need for column A once the last names have been exttracted, but I can't delete that column and have column B work.

I guess I could leave it like this and still have it work for my final step.

My final formula is to write a code that will pair the name "Andrus" in workbook A to the name "Andrus" in workbook B (B is the one with the extracted last name formula)...

in summary, I am trying to write a macro for all of this
After importing data from web:
1. Extract last names from column A to column B (done)
2. sort column B and corresponding data in each row alphabetically (done)
3. if name "Andrus" in column A of worksheet A matches "Andrus" in column B in worksheet B, then import cell L3 from worksheet B into O4 in worksheet A

can I, or should I, name different columns like "1st P" etc, so that it could match the name and then read the value in the named column?

I hope that is easily understood.

Again, I can't thank you enough

[Richard Buttrey]

Hi,

Once you've copied the formulae down column B you can then copy B3:B27 and immediately use paste special values to convert the col. B formulae to values. You can then delete column A if you wish.

You can use a combination of =MATCH() and =INDEX() to obtain your sheet B values.

i.e. in O4 of sheet A

Please Login or Register  to view this content.

You may need to play around with the cell refs. since it's not quite clear where your data lies. Copy the formula to other columns and adjust the col. refs. as necessary.

It would have been easier if you had uploaded an example workbook.

Regards