I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Select the list Data Filter Advanced Filter Copy to another range
Copy to 'click destionation cell" OK

HTH
Regards,
Howard

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Sub MakeUnique()'put a header in row 1
With Range("M1:M" & Cells(Rows.Count, "m").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("n1")
ActiveSheet.ShowAllData
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Sweet! you are the man! It worked beautfully....

"Don Guillett" wrote:

Sub MakeUnique()'put a header in row 1
With Range("M1:M" & Cells(Rows.Count, "m").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("n1")
ActiveSheet.ShowAllData
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Don-

If i wanted the put the new list in worksheet name Reference Data in cell d7
instead of on the same sheet, how would I do that? I thank you in advance

"Don Guillett" wrote:

Sub MakeUnique()'put a header in row 1
With Range("M1:M" & Cells(Rows.Count, "m").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("n1")
ActiveSheet.ShowAllData
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Dynamic With formula:

Rng =OffSet(Feuil1!$A$2,,,CountA(Feuil1!$A:$A))

In C2:
=INDEX(Rng,MIN(IF(Rng<"",IF(COUNTIF(C$1:C1,Rng)=0 ,ROW(INDIRECT
("1:"&ROWS(Rng))),ROWS(Rng)))))
Valid with Shift+ctrl+Enter

http://cjoint.com/?jtgonUhisn

JB
http://boisgontierjacques.free.fr/


On 18 sep, 22:54, David T wrote:
I have a list of items in column A and I need to create a drop down list in
column B *that excludes any duplicate data. *Does anyone have a macro that
can do this?

Column A *Column B
apple * * * *{drop down list inserted here}
pears
orange
apple
orange

Sorted list with UDF function:

http://boisgontierjacques.free.fr/fi...oublonsVBA.xls

JB

On 19 sep, 06:17, JB wrote:
Dynamic With formula:

Rng * * =OffSet(Feuil1!$A$2,,,CountA(Feuil1!$A:$A))

In C2:
=INDEX(Rng,MIN(IF(Rng<"",IF(COUNTIF(C$1:C1,Rng)=0 ,ROW(INDIRECT
("1:"&ROWS(Rng))),ROWS(Rng)))))
Valid with Shift+ctrl+Enter

http://cjoint.com/?jtgonUhisn

JBhttp://boisgontierjacques.free.fr/

On 18 sep, 22:54, David T wrote:



I have a list of items in column A and I need to create a drop down list in
column B *that excludes any duplicate data. *Does anyone have a macro that
can do this?

Column A *Column B
apple * * * *{drop down list inserted here}
pears
orange
apple
orange- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Excel 101. Just change the copy destination.
..Copy Sheets("yoursheetnamehere").Range("d7")
Now, if you are going to use this as for a data validation listNAME itthen
refer to =mynamedlist

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
Don-

If i wanted the put the new list in worksheet name Reference Data in cell
d7
instead of on the same sheet, how would I do that? I thank you in advance

"Don Guillett" wrote:

Sub MakeUnique()'put a header in row 1
With Range("M1:M" & Cells(Rows.Count, "m").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("n1")
ActiveSheet.ShowAllData
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list
in
column B that excludes any duplicate data. Does anyone have a macro
that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Thanks!

"Don Guillett" wrote:

Excel 101. Just change the copy destination.
..Copy Sheets("yoursheetnamehere").Range("d7")
Now, if you are going to use this as for a data validation listNAME itthen
refer to =mynamedlist

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
Don-

If i wanted the put the new list in worksheet name Reference Data in cell
d7
instead of on the same sheet, how would I do that? I thank you in advance

"Don Guillett" wrote:

Sub MakeUnique()'put a header in row 1
With Range("M1:M" & Cells(Rows.Count, "m").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Range("n1")
ActiveSheet.ShowAllData
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David T" wrote in message
...
I have a list of items in column A and I need to create a drop down list
in
column B that excludes any duplicate data. Does anyone have a macro
that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange

Thanks.

"JB" wrote:

Sorted list with UDF function:

http://boisgontierjacques.free.fr/fi...oublonsVBA.xls

JB

On 19 sep, 06:17, JB wrote:
Dynamic With formula:

Rng =OffSet(Feuil1!$A$2,,,CountA(Feuil1!$A:$A))

In C2:
=INDEX(Rng,MIN(IF(Rng<"",IF(COUNTIF(C$1:C1,Rng)=0 ,ROW(INDIRECT
("1:"&ROWS(Rng))),ROWS(Rng)))))
Valid with Shift+ctrl+Enter

http://cjoint.com/?jtgonUhisn

JBhttp://boisgontierjacques.free.fr/

On 18 sep, 22:54, David T wrote:



I have a list of items in column A and I need to create a drop down list in
column B that excludes any duplicate data. Does anyone have a macro that
can do this?

Column A Column B
apple {drop down list inserted here}
pears
orange
apple
orange- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -