Making list with unique columns

[Adam]

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?

 

[Naomi]

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

 

[Adam]

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

"Naomi" skrev:

 

[Naomi]

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-

 

[Naomi]

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.....

 

[Adam]

thanks, problem solved!

"Naomi" skrev:

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

Should be more like

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.....

 

[Jason Morin]

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

 

[Adam]

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: