# Automatically Alphabetize Names

#### hajiali

##### Board Regular
Hello All In sheet 1 I have the following

 HELPER 2 FIRST NAME HELPER 1 LAST NAME LAST, FIRST 1 BILLY 6 SMITH SMITH, BILLY 8 ZEE 7 WORD WORD, TONY 7 WILL 2 HAYES HAYES, WILL 3 PAUL 3 MORRIS MORRIS, PAUL 6 TAMMY 7 WORD WORD, ZEE 5 TIM 4 PARKER PARKER, TIM 2 JACK 5 PRICE PRICE, JACK 4 RANDY 1 CALVIN CALVIN, RANDY

<tbody>
</tbody>

Where Column A =IF(ISBLANK(B2),"",COUNTIF(\$B\$2:\$B\$7,"<="&\$B2))
Column C =IF(ISBLANK(D2),"",COUNTIF(\$D\$2:\$D\$7,"<="&\$D2))
Column E = Concentrate formula

In another sheet Cell A2 I have the Following

Code:
``=IFERROR(INDEX('Sheet1'!\$E\$2:\$E\$1000,MATCH(AGGREGATE(15,6,'Sheet1'!\$D\$2:\$D\$8,ROW(A1)),'Sheet1'!\$D\$2:\$D\$8,0)),"")``
this pulls and Sorts all Concentrate name based on last name in alphabetical order. My problems arise when there are two of the same last name.

Is there a way to add a function to above code where if there are duplicate last name to then check the first name of the duplicate and then sort alphabetical with last then first name.

Any suggestions would be greatly appreciated

Thanks

#### sandy666

##### Well-known Member
with Power Query aka Get&Transform

 FIRST LAST FIRST LAST Last, First BILLY SMITH RANDY CALVIN CALVIN, RANDY ZEE WORD WILL HAYES HAYES, WILL WILL HAYES PAUL MORRIS MORRIS, PAUL PAUL MORRIS TIM PARKER PARKER, TIM TAMMY WORD JACK PRICE PRICE, JACK TIM PARKER BILLY SMITH SMITH, BILLY JACK PRICE TAMMY WORD WORD, TAMMY RANDY CALVIN ZEE WORD WORD, ZEE

Code:
``````[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sort = Table.Sort(Source,{{"LAST", Order.Ascending}, {"FIRST", Order.Ascending}}),
Merge = Table.AddColumn(Sort, "Last, First", each Text.Combine({[LAST], [FIRST]}, ", "), type text)
in
Merge[/SIZE]``````
this is NOT vba

#### mikerickson

##### MrExcel MVP
If Last,First is in column E. A helper column with =COUNTIF(E:E,"<="&E1)+ROW(E1)/10000 will give each row a unique number.
Sorting on that helper column, by a formula similar to your current one, will return the list in alphabetical order and include all duplicate names.

#### hajiali

##### Board Regular
I'am assuming this is an Add-on for excel that needs to be download?
If I'm using this file on multiple computers and multiple users will i need to download on all computers for all users?
How do I get this and what is the process to inputting the above code?

Last edited:

#### hajiali

##### Board Regular
If Last,First is in column E. A helper column with =COUNTIF(E:E,"<="&E1)+ROW(E1)/10000 will give each row a unique number.
Sorting on that helper column, by a formula similar to your current one, will return the list in alphabetical order and include all duplicate names.
In that case will I only need the helper column for Column E and not the others?

#### sandy666

##### Well-known Member
I'am assuming this is an Add-on for excel that needs to be download?
it depends on the version of Excel you use, if 2010/2013 add-in is required, if higher - PowerQuery is build-in

#### mikerickson

##### MrExcel MVP
In that case will I only need the helper column for Column E and not the others?
If you want it alphabetized Last, First then yes.

### Forum statistics

1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...