Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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 -

Create list with macro
 

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

Create list with macro
 

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

Create list with macro
 

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 -