How do I create a unique sorted list?

[sroberts]

I successfully used the following functions to create a unique list of "units" (store numbers in a multi-store list) thanks to help from this site. However the list that is created is not in alphanumeric sort and I need the units to be sorted alphanumerically - hopefully without having to click on some sort macro... is there a way to modify this formula to return a sorted list?

=IF(ROWS($A$3:A3)<=$A$1,INDEX(Unit,
SMALL(IF(FREQUENCY(IF(Unit<>"",
MATCH("~"&Unit&"",Unit&"",0)),
ROW(Unit)-ROW(Sheet1!$AC$6)+1),ROW(Unit)-ROW(Sheet1!$AC$6)+1),
ROWS($B$3:B3))),"")

 

[Domenic]

It would be easier, and more efficient, to use Excel's sort feature. However, if you'd like a formula solution, and the data contains numerical values, try...

=IF(ROWS($A$3:A3)<=$A$1,SMALL(IF(FREQUENCY(IF(Unit<>"",MATCH("~"&Unit,Unit&"",0)),ROW(Unit)-MIN(ROW(Unit))+1),Unit),ROWS(A$3:A3)),"")

If the data contains text values (or a mix of text and numerical values), try...

=IF(ROWS($A$3:A3)<=$A$1,INDEX(Unit,MATCH(SMALL(IF(FREQUENCY(IF(Unit<>"",MATCH("~"&Unit,Unit&"",0)),ROW(Unit)-MIN(ROW(Unit))+1),MMULT(--(Unit>TRANSPOSE(Unit)),ROW(Unit)^0)),ROWS(A$3:A3)),MMULT(--(Unit>TRANSPOSE(Unit)),ROW(Unit)^0),0)),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[SydneyGeek]

Alternatively, build a one-column pivot table. You can then copy and paste as values, to be left with just the list

Denis

 

[sroberts]

SydneyG and Domenic -

Thanks for the suggestions - trying them now. - Steve

 

[Domenic]

It would be easier, and more efficient, to use Excel's sort feature.

Make that...

1) 'Use Advanced Filter > Unique records only' for a list of unique values.

2) Use 'Sort' to list values in alpha/numeric order.

 

[Fazza]

Another option, maybe, is a query table.

"SELECT DISTINCT unit FROM [Sheet1$]"

 

[sroberts]

is the Select Distinct a VB command? I am familiar with the SQL statement but have never used VB.

 

[Fazza]

I am familiar with VBA but don't know VB per se. AFAIK, "SELECT DISTINCT" will only have any meaning in SQL.