A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Making list with unique columns



 
 
Thread Tools Display Modes
  #1  
Old March 10th, 2005, 01:13 PM
Adam
external usenet poster
 
Posts: n/a
Default Making list with unique columns

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?
  #2  
Old March 10th, 2005, 01:31 PM
Naomi
external usenet poster
 
Posts: n/a
Default

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?

  #3  
Old March 10th, 2005, 02:13 PM
Adam
external usenet poster
 
Posts: n/a
Default

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?

  #4  
Old March 10th, 2005, 02:23 PM
Naomi
external usenet poster
 
Posts: n/a
Default

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?

  #5  
Old March 10th, 2005, 02:31 PM
Naomi
external usenet poster
 
Posts: n/a
Default

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?

  #6  
Old March 10th, 2005, 02:47 PM
Adam
external usenet poster
 
Posts: n/a
Default

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?

  #7  
Old March 10th, 2005, 03:26 PM
Jason Morin
external usenet poster
 
Posts: n/a
Default

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

  #8  
Old March 11th, 2005, 10:21 AM
Adam
external usenet poster
 
Posts: n/a
Default

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
E-mail Display name is wrong Jane Nangle Contacts 9 February 4th, 2005 11:05 AM
Pull unique names for drop down list [email protected] General Discussion 3 February 1st, 2005 11:23 PM
drop down list multiple columns c General Discussion 9 January 27th, 2005 04:13 PM
Union Query Not Returning A Value Jeff G Running & Setting Up Queries 2 October 19th, 2004 05:47 PM
Trying to list unique values in an arryay Dan S General Discussion 4 June 22nd, 2004 03:29 AM


All times are GMT +1. The time now is 12:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.