Automatically Alphabetize Names

hajiali

Board Regular
Joined
Sep 8, 2018
Messages
162
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,822
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,801
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
Joined
Sep 8, 2018
Messages
162
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
Joined
Sep 8, 2018
Messages
162
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
Joined
Oct 24, 2015
Messages
3,822
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top