I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

The simplest way, if you are only doing this once, is to use the advanced
filter. DataFilterAdvanced filter then follow the wizard but make sure that
you tick 'unique records only'.

"Adam" wrote:

I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

Thanks, and if I would like to use a formula?

"Naomi" skrev:

The simplest way, if you are only doing this once, is to use the advanced
filter. DataFilterAdvanced filter then follow the wizard but make sure that
you tick 'unique records only'.

"Adam" wrote:

I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

Dim i as integer
i = 5 (the row where your values begin)

Do until cells(i,1).value = "" (as long as your data is in column 1)
cells(i,1).copy
with selection
.Copy
.Insert Shift:=xlDown
end with
cells(i,1)=(i-id)+1
cells(i+1,1)=(i-id)+2
i = i+2
loop
You will need to write a macro. Something like-



"Adam" wrote:

Thanks, and if I would like to use a formula?

"Naomi" skrev:

The simplest way, if you are only doing this once, is to use the advanced
filter. DataFilterAdvanced filter then follow the wizard but make sure that
you tick 'unique records only'.

"Adam" wrote:

I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

Sorry! - just pasted in some code from somewhere else and posted by mistake!!

Should be more like

Dim i as integer
i = 5 (the row where your values begin)

Do until cells(i,1).value = "" (as long as your data is in column 1)
if cells(i,1)=cells(i+1,1) then
else
cells(i,1).copy
cells(i,5).pastespecial xlvalues (this pastes into column E)
endif
i=i+1

loop
Range("E5:E1000").sort Key1:=Range("E5"), Order1:=xlAscending


You could develop this macro a lot further to keep it in order etc.....



cells(i,1).copy
with selection
.Copy
.Insert Shift:=xlDown
end with
cells(i,1)=(i-id)+1
cells(i+1,1)=(i-id)+2
i = i+2
loop

"Naomi" wrote:

Dim i as integer
i = 5 (the row where your values begin)

Do until cells(i,1).value = "" (as long as your data is in column 1)
cells(i,1).copy
with selection
.Copy
.Insert Shift:=xlDown
end with
cells(i,1)=(i-id)+1
cells(i+1,1)=(i-id)+2
i = i+2
loop
You will need to write a macro. Something like-



"Adam" wrote:

Thanks, and if I would like to use a formula?

"Naomi" skrev:

The simplest way, if you are only doing this once, is to use the advanced
filter. DataFilterAdvanced filter then follow the wizard but make sure that
you tick 'unique records only'.

"Adam" wrote:

I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

thanks, problem solved!

"Naomi" skrev:

Sorry! - just pasted in some code from somewhere else and posted by mistake!!

Should be more like

Dim i as integer
i = 5 (the row where your values begin)

Do until cells(i,1).value = "" (as long as your data is in column 1)
if cells(i,1)=cells(i+1,1) then
else
cells(i,1).copy
cells(i,5).pastespecial xlvalues (this pastes into column E)
endif
i=i+1

loop
Range("E5:E1000").sort Key1:=Range("E5"), Order1:=xlAscending


You could develop this macro a lot further to keep it in order etc.....



cells(i,1).copy
with selection
.Copy
.Insert Shift:=xlDown
end with
cells(i,1)=(i-id)+1
cells(i+1,1)=(i-id)+2
i = i+2
loop

"Naomi" wrote:

Dim i as integer
i = 5 (the row where your values begin)

Do until cells(i,1).value = "" (as long as your data is in column 1)
cells(i,1).copy
with selection
.Copy
.Insert Shift:=xlDown
end with
cells(i,1)=(i-id)+1
cells(i+1,1)=(i-id)+2
i = i+2
loop
You will need to write a macro. Something like-



"Adam" wrote:

Thanks, and if I would like to use a formula?

"Naomi" skrev:

The simplest way, if you are only doing this once, is to use the advanced
filter. DataFilterAdvanced filter then follow the wizard but make sure that
you tick 'unique records only'.

"Adam" wrote:

I have a column with many different supplier, with some of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring only once if I do not
want to use the pivot table?

With your values in col. A starting in A1, copy this into
B1, press ctrl + shift + enter, and fill down until you
see error values:

=INDEX($A$1:$A$100,SMALL(IF(ROW($A$1:$A$100)=MATCH
($A$1:$A$100,$A$1:$A$100,0),ROW($A$1:$A$100)),ROW( )))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a column with many different supplier, with some
of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring
only once if I do not
want to use the pivot table?
.

If I press ctrl+shift+enter get the #N/A message already in the first row. If
I do not press the ctrl+shift+enter the first row is OK but in the rest I get
#NUM. Any ideas?

"Jason Morin" skrev:

With your values in col. A starting in A1, copy this into
B1, press ctrl + shift + enter, and fill down until you
see error values:

=INDEX($A$1:$A$100,SMALL(IF(ROW($A$1:$A$100)=MATCH
($A$1:$A$100,$A$1:$A$100,0),ROW($A$1:$A$100)),ROW( )))

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a column with many different supplier, with some
of the occuring many
times e.g.

Column A
Supplier A
Supplier A
Supplier B
Supplier C
Supplier C
Supplier C
Supplier D etc etc.

How do I make a list with all the supplier, occuring
only once if I do not
want to use the pivot table?
.