RE: "tough" Excel thing

["cwest" <cwest@xxxxxxxxxxxx>]

Thanks to those who had suggestions. I chose Kim's idea without getting into a VBA thing. It works quite well although it's amazing how much Excel slows as the grid grows and computations stack up :). 

Colin

-----Original Message-----
From: Kim [mailto:krf01@xxxxxxxxxxxxxxx] 
Sent: Wednesday, July 16, 2003 9:42 AM
To: cwest; omega-list@xxxxxxxxxx
Subject: Re: "tough" Excel thing

Colin,

I don't know why you are getting a few duplicates using the unique filter
feature. I can't reproduce that but a couple of things that you could try
are :

1. Try copying the uniquely filtered column to another location.
    Select the column with your symbol names - From the menu, go to Data -
Filter - Advanced Filter, select "Copy to new location",
    insert a Copy to location, leave the "Critiera" field empty, and check
"Unique Entries Only".

2. Another, more automated process will require either some VBA code as has
been suggested or you could try something like this
    using a few columns:

Given the following 12 symbols in Column A:

AAA
BBB
CCC
AAA
ZZZ
CCC
AAA
BBB
CCC
YYY
BBB
CCC

First, sort into order giving:

AAA
AAA
AAA
BBB
BBB
BBB
CCC
CCC
CCC
CCC
YYY
ZZZ

Insert this formula into cell B1 and copy thru cell B12:
=IF(COUNTIF($A$1:A1,A1)>1,"",A1) will give

AAA


BBB


CCC



YYY
ZZZ

Insert this formula into cell C1 and copy thru cell C12:

=IF(ROW()-ROW($C$1:$C$12)+1>ROWS($B$1:$B$12)-COUNTBLANK($B$1:$B$12),"",INDIR
ECT(ADDRESS(SMALL((IF($B$1:$B$12<>"",ROW($B$1:$B$12),ROW()+ROWS($B$1:$B$12))
),ROW()-ROW($C$1:$C$12)+1),COLUMN($B$1:$B$12),4)))

NOTE: This is an array formula and after entering or amending it, you have
to press CTRL/SHIFT/ENTER rather than just ENTER. This will embed the
formula in curly brackets {}. Column C should now look like this:

AAA
BBB
CCC
YYY
ZZZ

Tinker with as required.

Rgds,

Kim

----- Original Message ----- 
From: "cwest" <cwest@xxxxxxxxxxxx>
To: <omega-list@xxxxxxxxxx>
Sent: Wednesday, July 16, 2003 11:30 AM
Subject: "tough" Excel thing


> Here’s another tough thing to do in Excel (for me at least) that perhaps
someone knows how to address. I’m loading transactions (trades) into excel
and I’d like to build a column of unique symbol names, and sort them. I
tried the unique filter feature; but that doesn’t seem to be reliable. It
allows a few duplicates. I’ve tested that at some length.
>
> A function like this would be good (Mr. Gates) ☺.
>
> =unique(range1, range2, sort –a).
>
> Any suggestions how to do this would be greatly appreciated.
>
> Thanks in advance
> Colin West
>
>
>