Create a list without duplicates

[coccio]

I have a list of street addresses and want to create a list of street addresses without any duplicate address show for instance in this example
I want to turn column A into Column B

COLUMN A	COLUMN B
ADAMS ST	ADAMS ST
ADAMS ST	ALBERT AVE
ADAMS ST	ALMONT AVE
ALBERT AVE
ALMONT ST
ALMONT ST
ALMONT ST

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[johnnyL]

You could do it all in the same column with one line of code:

    ActiveSheet.Range("$A$1:$A$7").RemoveDuplicates Columns:=1, Header:=xlNo

 

[rlv01]

Knowing your Excel version is important. If you are using a more recent version of Excel, then under the "Data" menu there is a pick called "Remove Duplicates"

 

[coccio]

Knowing your Excel version is important. If you are using a more recent version of Excel, then under the "Data" menu there is a pick called "Remove Duplicates"

I updated my profile I have 2016

 

[coccio]

You could do it all in the same column with one line of code:

    ActiveSheet.Range("$A$1:$A$7").RemoveDuplicates Columns:=1, Header:=xlNo

I was looking for a formula.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B
1	COLUMN A	Distinct
2	ADAMS ST	ADAMS ST
3	ADAMS ST	ALBERT AVE
4	ADAMS ST	ALMONT ST
5	ALBERT AVE
6	ALMONT ST
7	ALMONT ST
8	ALMONT ST
9
10
Main

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($A$2:$A$100<>"")/(ISNA(MATCH($A$2:$A$100,B$1:B1,0))),1)),"")

 

[coccio]

Thanks for that.
How about

Fluff.xlsm
	A	B
1	COLUMN A	Distinct
2	ADAMS ST	ADAMS ST
3	ADAMS ST	ALBERT AVE
4	ADAMS ST	ALMONT ST
5	ALBERT AVE
6	ALMONT ST
7	ALMONT ST
8	ALMONT ST
9
10
Main

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($A$2:$A$100<>"")/(ISNA(MATCH($A$2:$A$100,B$1:B1,0))),1)),"")

That worked Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.