Automatically Alphabetize Names

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
Hello All In sheet 1 I have the following

HELPER 2FIRST NAMEHELPER 1LAST NAMELAST, FIRST
1BILLY
6SMITHSMITH, BILLY
8ZEE7WORDWORD, TONY
7WILL2HAYESHAYES, WILL
3PAUL3MORRISMORRIS, PAUL
6TAMMY7WORDWORD, ZEE
5TIM4PARKERPARKER, TIM
2JACK5PRICEPRICE, JACK
4RANDY1CALVINCALVIN, 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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
with Power Query aka Get&Transform

FIRSTLASTFIRSTLASTLast, First
BILLYSMITHRANDYCALVINCALVIN, RANDY
ZEEWORDWILLHAYESHAYES, WILL
WILLHAYESPAULMORRISMORRIS, PAUL
PAULMORRISTIMPARKERPARKER, TIM
TAMMYWORDJACKPRICEPRICE, JACK
TIMPARKERBILLYSMITHSMITH, BILLY
JACKPRICETAMMYWORDWORD, TAMMY
RANDYCALVINZEEWORDWORD, 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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top