On 27 Oct 2005 11:53:38 -0700, "Nick Dangr" wrote:

Maybe there's an easy way to do this. A lot of my excel work revolves
around data pulled from a live database. I'll have variable length
tables. My current issue has to do with a set of information as below.
(prepare for scrolling).

------------ SNIP ------------
07 Natural Remedies 8842 10-10
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 Renew Life 10253332 10-11-0
07 United 649464
07 United 649464
07 United 649464
07 United 649464
07 United 649464
07 United 649464
07 United 649464
07 United 649464 10-19-05CR
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 Nutraceutical 3220889.1 10-
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 TOL 103943768 10-19-05 CR
07 Select Nutrition 652686 10
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Sami's Bakery 14103 10-18-
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527 10-17-05
07 Now 568527CR 10-17-05
07 Now 568527CR 10-17-05
07 Now 568527CR 10-17-05
07 Natures Way 91045097 10-19
07 Nature's Way 91045114 10-19
07 Nature's Way 91045114 10-19
07 Nature's Way 91045114 10-19
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05
07 United 677582 10-26-05CR
07 United 677582 10-26-05CR
------------ SNIP ------------

What I'd like to do is take this column from sheet 1 and have the data
autofiltered to sheet 2 so it appears as such, removing repetitions:

------------ SNIP ------------
07 Natural Remedies 8842 10-10
07 Nature's Way 91045114 10-19
07 Natures Way 91045097 10-19
07 Now 568527 10-17-05
07 Now 568527CR 10-17-05
07 Nutraceutical 3220889.1 10-
07 Renew Life 10253332 10-11-0
07 Sami's Bakery 14103 10-18-
07 Select Nutrition 652686 10
07 TOL 103943768 10-19-05
07 TOL 103943768 10-19-05 CR
07 United 677582 10-26-05CR
07 United 649464
07 United 649464 10-19-05CR
07 United 677582 10-26-05
------------ SNIP ------------

The trick is, I want to see it happen automatically, and when the first
sheet refreshes, I'd like to have the 2nd sheet automatically refresh
also.

Any information is helpful. Thanks in advance - you folks are always a
great source of information.

O.B.D. Ben/ND

Well, you did say you wanted automatic updating. Here's one way that should
work. I am assuming all your data is in a single column.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. On sheet 2, select a one column range that is long enough to accommodate
all of the unique entries from sheet 1. It is OK to leave a number of blanks
at the end to allow for expansion. I selected A1:A150

3. Assuming your data begins in Sheet1!A1, and you want your results to start
in Sheet2!A1, enter into the formula bar: =UNIQUEVALUES(Sheet1!A1:An) where
n= the maximum row number that you have selected on sheet2. 'n' can be as
great as 65535.

4. After typing it in and with the range on sheet2 still selected, hold down
<ctrl<shift while hitting <enter. Excel will enter this as an array formula
in the entire selection. In each cell, the formula will appear the same, and
with braces {...} around the formula.

5. The results will be sorted in descending order. If you want them sorted in
ascending order, there is an optional argument to the function: e.g.

=UNIQUEVALUES(Sheet1!A1:A65535,1)

Since this is a volatile function, any changes made in sheet 1 will be
reflected in sheet 2 without further intervention.


--ron