Need a formula to remove duplicates from a list, and sort alphabetically

[freeb1893]

I have a list of words in array O2:O300 where I want to remove duplicates and sort the new list alphabetically starting in cell A1. Anyone know how to put this into a formula?

 

[AhoyNC]

This link should help.

https://www.youtube.com/watch?v=IZLAzIYfMDU

 

[minhhieule89]

=IFERROR(INDEX(list1,MATCH(MIN(IF(ISBLANK(list1),"",IF(COUNTIF($P$1:P1,list1)=0,1,MAX((COUNTIF(list1,"<"&list1)+1)*2))*(COUNTIF(list1,"<"&list1)+1))),IF(ISBLANK(list1),"",COUNTIF(list1,"<"&list1)+1),0)),"")

list1 is the name of the range you chose.

make sure that $P$1:P1 depends on which column you enter the formula in, if you enter the formula in column B, it should be $B$1:B1


just copy the formula down

FYI: it will be slow if it's a long list, just be patient

 

[freeb1893]

It doesn't seem to be working. It just lists 0's in each cell that I drag the formula down into.

list1 is the name of the range you chose.

make sure that $P$1:P1 depends on which column you enter the formula in, if you enter the formula in column B, it should be $B$1:B1


just copy the formula down

FYI: it will be slow if it's a long list, just be patient

 

[AhoyNC]

Did you try the formula that was in the link in post #2 above? It seems to do what you want (see example from video below).
Also, if you have a later ver. of Excel why not just copy the list to column A use remove duplicates from the menu under DATA and then sort.

Drag formula down as needed.
NOTE: This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	N	O	P
1	Unique Sorted List			List With Dups and Not Sorted
2	Chin			Jo
3	Jo			Joe
4	Joe			Sioux
5	John			Chin
6	Sioux			Sioux
7				Jo
8				Chin
9				John
10
11
Sheet2

 

[freeb1893]

Same issue. Formula just returns 0 for each cell.

Did you try the formula that was in the link in post #2 above? It seems to do what you want (see example from video below).
Also, if you have a later ver. of Excel why not just copy the list to column A use remove duplicates from the menu under DATA and then sort.

Drag formula down as needed.
NOTE: This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Sheet2

*	A	B	N	O	P
1	Unique Sorted List	*	*	List With Dups and Not Sorted	*
2	Chin	*	*	Jo	*
3	Jo	*	*	Joe	*
4	Joe	*	*	Sioux	*
5	John	*	*	Chin	*
6	Sioux	*	*	Sioux	*
7	*	*	*	Jo	*
8	*	*	*	Chin	*
9	*	*	*	John	*
10	*	*	*	*	*
11	*	*	*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 140px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 155px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula A2 {=IFERROR(INDEX($O$2:$O$300,MATCH(0,COUNTIF($O$2:$O$300,"<"&$O$2:$O$300)-SUM(COUNTIF($O$2:$O$300,$A$1:A1)),0)),"")} <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You need to post a small sample of your data so we can see what you are dealing with.

 

[Bwachtel]

You need to post a small sample of your data so we can see what you are dealing with.

I am having the same issues, well a little different. It appears to be sorting correctly, besides the 35A-35D being at the beginning but i can live with that. But my problem is that values are missing such as 66 and 99. Any help would be greatly appreciated!

My formula in sorted column is

=INDEX(nn,MATCH(0,COUNTIF(nn,"<"&nn)-SUM(COUNTIF(nn,$G$3:G3)),0))

Where nn is a dynamic range for the sorted column.

Unsorted is Column F Row 3 and Sorted is Column G Row 3.

Unsorted Sorted 3.00 35A 5.00 35B 9.00 35C 12.00 35D 17.00 3.00 32.00 5.00 50.00 9.00 18.00 12.00 35A 17.00 35B 18.00 84.00 20.00 17.00 22.00 35C 30.00 49.00 32.00 66A 48.00 99A 49.00 30.00 50.00 48.00 51.00 55.00 55.00 66B 60.00 127.00 84.00 22.00 127.00 35D #N/A 51.00 #N/A 66C #N/A 99B #N/A 1.00 #N/A 3.00 #N/A 5.00 #N/A 9.00 #N/A 17.00 #N/A 32.00 #N/A 50.00 #N/A 60.00 #N/A 1.00 #N/A 3.00 #N/A 5.00 #N/A 9.00 #N/A 17.00 #N/A 20.00 #N/A 32.00 #N/A 48.00 #N/A 60.00 #N/A 9.00 #N/A 17.00 #N/A 20.00 #N/A 32.00 #N/A 48.00 #N/A 1.00 #N/A 3.00 #N/A 5.00 #N/A 9.00 #N/A 17.00 #N/A 32.00 #N/A 50.00 #N/A 1.00 #N/A 3.00 #N/A 5.00 #N/A 9.00 #N/A 17.00 #N/A 32.00 #N/A 48.00 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A <tbody> </tbody>

<tbody>
</tbody>

 

[Bwachtel]

So i have determined that the above code does work, but the 35A, 35B, etc is causing issues with the formula. I could change the 35A to 35.001 to fix it, but that would not be a preferred solution.