Formula to list unique entries

[Rob]

I know how to do this using VBA code, but want some ideas
as to whether its possible using only formulae ...

I have a list of identifiers - they're purchase order
numbers - in a column and there are multiple entries for
each unique number. What I want to do is have another
column with just one of each of the unique numbers in a
contiguous list i.e. without any gaps in between.

I can do this with code to sort, check, delete etc., and
know it can be done using pivottables, but is there a
simple (or complex!) formula/formulae that could do this?

Any suggestions much appreciated.

Rob

 

[Dave Peterson]

If your real question is how to get the list quickly with not too much work, you
can use Data|filter|Advanced filter.

Debra Dalgleish has some nice instructions here:
http://www.contextures.com/xladvfilter01.html#FilterUR

 

[Dave Peterson]

Peo Sjoblom showed one way to do this using some extra columns.

You can see it all at Debra Dalgleish's site (she used it in Data|validation).

http://www.contextures.com/xlDataVal03.html

But say your data is in A1:A30.

then somewhere else (I used B1:b30), fill it with this formula:

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Then somewhere else (I used c1:c30), use Peo's formula:

=IF(ISNUMBER(SMALL(IF($B$1:$B$30="","",ROW($B$1:$B$30)),ROW(1:1))),
INDIRECT("A"&SMALL(IF($B$1:$B$30="","",ROW($B$1:$B$30)),ROW(1:1))),"")
(all one cell)
It's an array formula, so you hit ctrl-shift-enter instead of just enter.
If you do it correctly, excel will wrap curly brackets {} around the formula.

(I'd settle for the workbook_open even and some code. (but I could never have
come up with Peo's formula!))

 

[Rob]

Thanks.

I'd never even heard of array formulas before today. I
think I shall be investigating them a bit more as they
look pretty powerful.

Kind regards

Rob

 

[Aladin Akyurek]

The following link describes a multitude of possible methods...

http://216.92.17.166/board2/viewtopic.php?t=8659