<table>
<tr><td>Column A</td></tr>
<tr><td>A</td></tr>
<tr><td>B</td></tr>
<tr><td>C</td></tr>
<tr><td>C</td></tr>
<tr><td>C</td></tr>
<tr><td>B</td></tr>
<tr><td>B</td></tr>
</table>

I want another column to list the values from Column A, but only once (A,B,C). Is there a formula that will go down the column and only list the values once. I want to avoid using a macro if possible.

 

:ibanachosticwrtitesavbscript:

 

+1

you could use the concatenate function if column A's number of rows is static..

=CONCATENATE(A1,",",A2,",",A3,",",A4)

 

I can do it in VB, but this isn't for me, so I'd like to keep it away from VB. I think the only way to do it is VB though.

 

List won't be static.. and the rows will be added randomly (A,C,B,B,C,C)

 

I see a lot of mentioning of using Advanced Filter. Does this have to be real-time updating?

 

Worksheet 2 will be linked to Worksheet 1. Worksheet 1 will contain the list of Column A, while Worksheet 2 will just need A,B,C listed even if A appears 50 times.

 

Excel 2007 has this built in. Data ribbon, Data Tools section, "Remove Duplicates".

 

Not much help, but this would be trivial in Access. SQL has the DISTINCT keyword to do just what you want. It's a shame Excel doesn't have something similar.

 

Not what I need, but thanks.

Yeah wish it were in Access.

 

Pivot table?

 

No..

 

Excel 2007?

I've been playing around trying to use a linked datasource back to the existing file and using MS Query to get a distinct list of values from Sheet1. Yeah, it worked, but when I closed and reopened the file it couldn't reconnect the datasource. Might be because I saved as Excel 2003. I'm also not sure what would happen if the file was moved from its original location.

Might be something for you to try out.

 

Figured it out woooo

=IFERROR(IF(OR(G3="",G3=21000),"",SMALL(F3:F2579,C OUNTIF(F3:F2579,"<="&G3)+1)),"")

Don't know what the hell it means, but it works.

 

:headExplodes:

 

O don't worry I have no idea what that function does but it was on one of the tabs in the file.. I played around with it and now it works. Even after some research I still don't know what it does.