Re: "tough" Excel thing

["Kim" <krf01@xxxxxxxxxxxxxxx>]

Yep! Array formulas can really slow things down.

Personally, I prefer to use MS Access instead of Excel wherever possible but
even if you want to display/manipulate the data in Excel, you can still use
Access's database engine to do the grunt work without having to write any
code.

Create a Table with one Text Field to house the symbols.
Drop your column of symbols into the new table.
Create a very simple query to sort/filter out duplicates using the 'Group
by' and 'Sort' options in the query window.
This will produce the following SQL

SELECT YourSymbolFieldName
FROM Your SymbolTableName
GROUP BY SymbolTableName.SymbolFieldName
ORDER BY SymbolTableName.SymbolFieldName;

Then link the column in Excel to that query using Data/Get External Data/New
Database Query.
In performance terms - there is no contest.

Rgds,

Kim

----- Original Message ----- 
From: "cwest" <cwest@xxxxxxxxxxxx>
To: "'Kim'" <krf01@xxxxxxxxxxxxxxx>; <omega-list@xxxxxxxxxx>
Sent: Thursday, July 17, 2003 11:28 AM
Subject: RE: "tough" Excel thing


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