Unique alphabetical list, omitting blank cells

[rgillson3]

My company does a "Daily Cash Sheet", to record tips between servers and bartenders or bussers.

They list the names, in no particular order in cells G2:G15.

I want to extract the names in alphabetical order, ignoring any blanks into a separate list.

So, the following names in G2:G15:

Jim

Kim

Fred

Dave

Would be returned in L2:L15 as:

Dave
Fred
Jim
Kim

I've hunted around a LOT, and can't find anything that works completely, even more complicated formulas.

Can someone please provide a simpler formula that will do this?

Thank you.

 

[rgillson3]

I've ran into a couple occurrences an identical name is listed twice within the range. When this occurs, the list "stops", meaning it won't return any names. If I add even one letter to the end of either occurrence, it starts working again.

Is there a way to adjust the formula to account for this?

 

[sandy666]

do you want the result without duplicates or with duplicates ?

 

[DanteAmor]

Here another macro for you to consider

Sub Unique_alphabetical()
  Dim coll As Object, c As Range, dict As Object, ky As Variant
  Set coll = CreateObject("System.Collections.ArrayList")
  Set dict = CreateObject("Scripting.Dictionary")
  For Each c In Range("A38:E44").SpecialCells(xlCellTypeConstants)
    dict(c.Value) = Empty
  Next
  For Each ky In dict.keys
    coll.Add ky
  Next
  coll.Sort
  Range("G38").Resize(dict.Count).Value = Application.Transpose(coll.toArray)
End Sub

HOW TO INSTALL MACROs

If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Unique_alphabetical) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[rgillson3]

sandy666 - I'd prefer without duplicates. But, I can work with them if necessary.

DanteAmor - Thank you for the Macro. However, I'd prefer to stick with some kind of a formula that will just "create" the list for me. My end-goal is to have this be something that any coworker can easily work with.

 

[Eric W]

You're really asking a lot of a formula! Here's what I came up with:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1							Sorted name
2	Zeke	Mark	Jill	Jill	Paul		Amy
3		Bob	Amy	Bob			Bob
4							Jill
5							Mark
6							Paul
7							Zeke
8
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=IFERROR(INDIRECT(TEXT(10000*MOD(AGGREGATE(15,6,EXP(LN(COUNTIF($A$2:$E$3,"<="&$A$2:$E$3)-SUM(COUNTIF($G$1:$G1,$A$2:$E$3))-1+((ROW($A$2:$E$3)*100+COLUMN($A$2:$E$3)))/10000)),1)+1,1),"R00C00"),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

However, I tried it on your A38:E44 range with some data in between, and it did not work right. It included some of those values, and excluded some of the names. If you've got Excel 365 with the new functions, there might be some more options.

But you're really into VBA territory now. Dante's macro might work for you, or I could update my UDF so that it appears to be a formula on your sheet, so maybe your co-workers can handle it, but you'll still need to install the code.

 

[sandy666]

@sandy666 - I'd prefer without duplicates. But, I can work with them if necessary.

Column1	Column2	Column3	Column4	Column5		Column1
Zeke			Jill	Paul		Amy
				Bob		Bob
	Paul					Jill
Bob						Paul
			Amy			Zeke
	Bob	Amy

// Table1
let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Expand = Table.ExpandListColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1"),
    Distinct = Table.Distinct(Table.Sort(Table.SelectRows(Expand, each ([Column1] <> null)),{{"Column1", Order.Ascending}})),
    TSC = Table.SelectColumns(Table.AddColumn(Distinct, "Text", each Text.Select([Column1],{"a".."z","A".."Z"})),{"Text"})
in
    TSC

 

[Eric W]

This version will ignore non-text values on a split range:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1							Sorted name
2	Zeke	Mark	Jill	Jill	Paul		Amy
3							Bob
4		1	2	555.21			Jill
5							Mark
6		Bob	Amy	Bob	Mark		Paul
7							Zeke
8
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	=IFERROR(INDIRECT(TEXT(10000*MOD(AGGREGATE(15,6,EXP(LN(ISTEXT($A$2:$E$6)*(COUNTIF($A$2:$E$6,"<="&$A$2:$E$6)-SUM(COUNTIF($G$1:$G1,$A$2:$E$6)))-1+((ROW($A$2:$E$6)*100+COLUMN($A$2:$E$6)))/10000)),1)+1,1),"R00C00"),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[DanteAmor]

Thank you for the Macro. However, I'd prefer to stick with some kind of a formula that will just "create" the list for me. My end-goal is to have this be something that any coworker can easily work with.

It is also very simple to assign the macro to a button, in this case you would only have to press the button to update the list.

Or also every time you write a name in the range the macro can update the list, put the following code in the events of the sheet.

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, coll As Object, c As Range, dict As Object, ky As Variant
  Set r = Range("A38:E44")
  If Not Intersect(Target, r) Is Nothing Then
    If Target.CountLarge > r.Count Then Exit Sub
    Set coll = CreateObject("System.Collections.ArrayList")
    Set dict = CreateObject("Scripting.Dictionary")
    For Each c In r.SpecialCells(xlCellTypeConstants)
      dict(c.Value) = Empty
    Next
    For Each ky In dict.keys
      coll.Add ky
    Next
    coll.Sort
    Range("G38").Resize(dict.Count).Value = Application.Transpose(coll.toArray)
  End If
End Sub

__________________________________________________________________________________
Every time you change a name in the range, the list is automatically updated
__________________________________________________________________________________

 

[sandy666]

optimised version

Column1	Column2	Column3	Column4	Column5		Text
Zeke			Jill	Paul		Amy
	1			Bob		Bob
	Paul	,,	55			Jill
Bob		@				Paul
	135		Amy			Zeke
#				22
	Bob	Amy	?

// Table1
let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Expand = Table.ExpandListColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1"),
    Text = Table.AddColumn(Expand, "Text", each Text.Select([Column1],{"a".."z","A".."Z"})),
    Distinct = Table.Distinct(Table.SelectRows(Table.RemoveRowsWithErrors(Text, {"Text"}), each ([Text] <> null)), {"Text"}),
    TSC = Table.SelectColumns(Table.Sort(Table.SelectRows(Distinct, each ([Text] <> "")),{{"Text", Order.Ascending}}),{"Text"})
in
    TSC

after any change just right click on result (green) table and select Refresh

 

[DanteAmor]

Hi @rgillson3

If you want to test the code, just to see how it works, try this updated version to consider only texts:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, coll As Object, c As Range, dict As Object, ky As Variant
  Set r = Range("A38:E44")
  If Not Intersect(Target, r) Is Nothing Then
    If Target.CountLarge > r.Count Then Exit Sub
    Set coll = CreateObject("System.Collections.ArrayList")
    Set dict = CreateObject("Scripting.Dictionary")
    For Each c In r.SpecialCells(xlCellTypeConstants)
      If c.Value Like "*[A-Za-z]*" Then dict(c.Value) = Empty
    Next
    For Each ky In dict.keys
      coll.Add ky
    Next
    coll.Sort
    Range("G38").Resize(dict.Count).Value = Application.Transpose(coll.toArray)
  End If
End Sub