PDA

View Full Version : [SOLVED] Sort names NEWBIE



JLandrum
05-14-2015, 08:33 AM
Attached: How can I make a column automatically sort by last name (1st column) that contains first an last name and have D.O.H (3rd column) info stay with the name. A command button would also work for me, just not sure how to do it. Thank you

Yongle
05-14-2015, 10:22 AM
Names are easier to sort if you have one column for First Name and a different column for Last Name.
(Possible then sort first by Last Name followed by first name)


Use standard Excel functionality to put values in adjacent columns as follows:
If Peter Smith is in cell A3
=LEFT(A3,FIND(" ",A3,1)-1) returns value Peter
=RIGHT(A3,LEN(A3)-FIND(" ",A3,1)) returns value Smith
(beware - this works only in cases with one first name)


If it is not easy to modify worksheet structure, then input the names differently
Instead of Peter Smith, input as Smith,Peter
which will allow you to sort by the last name (and will automatically second sort on first name)

I have had a look at your workbook - column A only contains First Names

JLandrum
05-14-2015, 10:38 AM
Thanks for the help, I made 2 columns, first and last names. How can I have VBA automatically sort the names when names are input. Thanks again

Yongle
05-14-2015, 11:12 AM
I assume you want to sort first by Last Name and then by First Name
See attached file. Click on the button and see what happens.
I unmerged cells within data sort range - one of the merged cells was causing a problem
The macro is called SortBySurname - you will need to adapt to suit your column numbers and range

JLandrum
05-14-2015, 11:30 AM
That's great, works perfect. Thank again