Formula to list unique entries

  • Thread starter Thread starter Rob
  • Start date Start date
R

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
 
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!))
 
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
 
Back
Top