Values only once when multiple values

[Nebula2121]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Current
[/TD]
[TD]Desired Result
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Apple
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apple
[/TD]
[TD]Banana
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mango
[/TD]
[TD]Mango
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mango
[/TD]
[TD]Peach
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Mango
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Peach
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Banana
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Banana
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need the value only listed once (preferably in Alpha Order) from a data set column that lists that value multiple times. Refer to table for Current Set and the Desired Result; how do I get to the Desired Result?

 

[tyija1995]

Hey,

Excel has an inbuilt Remove duplicates tool in the Data ribbon tab. Then just sort the column alphabetically !

 

[Joe4]

One simple way it to copy the the list to column B, use Excel's built-in Remove Duplicate Functionality, and then sort that list.
If you wanted VBA to do it, you can turn on your Macro Recorder and record yourself performing the steps above, and then you will have the VBA required to do that.

 

[Nebula2121]

Is there a formula based answer that does not require VBA?

 

[tyija1995]

You could use COUNTIF as such:

=IF(COUNTIF($A$1:A1,A1)=1,A1,"") and drag down the B column.

It will leave spaces however but it will generate a unique list.

Then you can just tidy the spaces up pretty easily by sorting again.

 

[Joe4]

Here is an explanation of a formula to weed out duplicates:
https://www.extendoffice.com/documents/excel/4032-excel-dynamic-list-of-unique-values.html#a1

Here is one that seems to do both weed out dups and sorts:
https://www.extendoffice.com/documents/excel/3238-excel-unique-list-in-alphabetical-order.html

 

[DanteAmor]

You can create a pivot table, drag the field from column A in Row Lables

 

[AhoyNC]

Here is another link using a formula.
https://www.youtube.com/watch?v=IZLAzIYfMDU