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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
E-mail Display name is wrong | Jane Nangle | Contacts | 9 | February 4th, 2005 10:05 AM |
Pull unique names for drop down list | [email protected] | General Discussion | 3 | February 1st, 2005 10:23 PM |
drop down list multiple columns | c | General Discussion | 9 | January 27th, 2005 03: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 |