Automatically Alphabetize Names

[hajiali]

Hello All In sheet 1 I have the following

[TABLE="width: 500"]
<tbody>[TR]
[TD]HELPER 2[/TD]
[TD]FIRST NAME[/TD]
[TD]HELPER 1[/TD]
[TD]LAST NAME[/TD]
[TD]LAST, FIRST[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]BILLY
[/TD]
[TD]6[/TD]
[TD]SMITH[/TD]
[TD]SMITH, BILLY[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ZEE[/TD]
[TD]7[/TD]
[TD]WORD[/TD]
[TD]WORD, TONY[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]WILL[/TD]
[TD]2[/TD]
[TD]HAYES[/TD]
[TD]HAYES, WILL[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PAUL[/TD]
[TD]3[/TD]
[TD]MORRIS[/TD]
[TD]MORRIS, PAUL[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]TAMMY[/TD]
[TD]7[/TD]
[TD]WORD[/TD]
[TD]WORD, ZEE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TIM[/TD]
[TD]4[/TD]
[TD]PARKER[/TD]
[TD]PARKER, TIM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JACK[/TD]
[TD]5[/TD]
[TD]PRICE[/TD]
[TD]PRICE, JACK[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]RANDY[/TD]
[TD]1[/TD]
[TD]CALVIN[/TD]
[TD]CALVIN, RANDY[/TD]
[/TR]
</tbody>[/TABLE]

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

=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]

with Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]FIRST[/td][td=bgcolor:#5B9BD5]LAST[/td][td][/td][td=bgcolor:#70AD47]FIRST[/td][td=bgcolor:#70AD47]LAST[/td][td=bgcolor:#70AD47]Last, First[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BILLY[/td][td=bgcolor:#DDEBF7]SMITH[/td][td][/td][td=bgcolor:#E2EFDA]RANDY[/td][td=bgcolor:#E2EFDA]CALVIN[/td][td=bgcolor:#E2EFDA]CALVIN, RANDY[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ZEE[/td][td]WORD[/td][td][/td][td]WILL[/td][td]HAYES[/td][td]HAYES, WILL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]WILL[/td][td=bgcolor:#DDEBF7]HAYES[/td][td][/td][td=bgcolor:#E2EFDA]PAUL[/td][td=bgcolor:#E2EFDA]MORRIS[/td][td=bgcolor:#E2EFDA]MORRIS, PAUL[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PAUL[/td][td]MORRIS[/td][td][/td][td]TIM[/td][td]PARKER[/td][td]PARKER, TIM[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]TAMMY[/td][td=bgcolor:#DDEBF7]WORD[/td][td][/td][td=bgcolor:#E2EFDA]JACK[/td][td=bgcolor:#E2EFDA]PRICE[/td][td=bgcolor:#E2EFDA]PRICE, JACK[/td][/tr]

[tr=bgcolor:#FFFFFF][td]TIM[/td][td]PARKER[/td][td][/td][td]BILLY[/td][td]SMITH[/td][td]SMITH, BILLY[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JACK[/td][td=bgcolor:#DDEBF7]PRICE[/td][td][/td][td=bgcolor:#E2EFDA]TAMMY[/td][td=bgcolor:#E2EFDA]WORD[/td][td=bgcolor:#E2EFDA]WORD, TAMMY[/td][/tr]

[tr=bgcolor:#FFFFFF][td]RANDY[/td][td]CALVIN[/td][td][/td][td]ZEE[/td][td]WORD[/td][td]WORD, ZEE[/td][/tr]
[/table]

[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]

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]

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?

 

[hajiali]

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]

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]

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.