Unique values from a list

[Sean Bartleet]

Hi,

I have a list in rng1 (C1:C10) is there a formula that I can copy to E1:E?
that will list all the unique values in list 1. For example if I have the
numbers 1,1,2,2,3,3,4,4,5,5 in rng1 I would like the resulting list in E1:E5
to read 1,2,3,4,5.

Ideally this should also be able to sort the list, but I can do that with a
dummy list created using the small command and picking up on the row number
to get each successive kth smallest value but this will require a hidden
column which is undesirable.

I can do this with two dummy lists but the formulas are nasty. With my list
in C1:C10 I can extract unique entries to column D using the array formula
in cell D1 "=IF(COUNTIF($C$1:C1,C1)=1,C1,"")" copying this down to Cells
D210 I get the unique values. Then I can eliminate blank cells using a
formula I found at http://cpearson.com/excel/duplicat.htm long ago in cell
E1
"=IF(ROW()-ROW($E$1:$E$9)+1>ROWS($D$1:$D$10)-COUNTBLANK($D$1:$D$10),"",INDIR
ECT(ADDRESS(SMALL((IF($D$1:$D$10<>"",ROW($D$1:$D$10),ROW()+ROWS($D$1:$D$10))
),ROW()-ROW($E$1:$E$9)+1),COLUMN($D$1:$D$10),4)))" Again an array formula
copied down to cells E2:E10

In fact the numbers in cells C1:C10 come from a union of two lists in cells
A1:A6 and B1:B4 using the formula in cell C1
"=IF(CELL("row",A1)<=ROWS($A$1:$A$6),SMALL($A$1:$A$6,CELL("row",A1)),SMALL($
B$1:$B$4,CELL("row",A1)-ROWS($A$1:$A$6)))" copied down to cells C2:C10
(thanks to Ivano)

This is a bit clumsy and requires two dummy rows. Is there any easier way to
do this using formulas? What I am really looking for is a sorted set of
unique numbers resulting from the union of two lists or numbers.

Any assistance will be appreciated.

Thanks.

Sean Bartleet

 

[Bob Phillips]

Look at Data>Filter>Advanced Filter and Copy To Another Location and Unique
records.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi

One way without formula's is to do an advanced filter on
rng1 of your list (Data>Filter>Advanced Filter),
tick 'Copy to another location' and tick 'Unique Record
only'.

In column E you can then refer to the location you copied
your unique records to.

However, you would have to do this every time your list
changes.

Regards,
Hans

 

[Stephen Dunn]

Hi Sean,

I'm taking it that you want only the original two lists (in columns A and
B), and one column for the results (column E)...

Use this in E1:

=SMALL($A$1:$B$6,1)

and this in E2:

=IF(ROW()>SUMPRODUCT(($A$1:$B$6<>"")/
COUNTIF($A$1:$B$6,$A$1:$B$6&"")),"",
INDEX(SMALL($A$1:$B$6,ROW(INDIRECT("1:"&ROWS($A$1:$B$6)*2))),
MATCH(0,COUNTIF(E1:E$1,
SMALL($A$1:$B$6,ROW(INDIRECT("1:"&ROWS($A$1:$B$6)*2)))),0)))

as an array formula (hold Ctrl+Shift when you press Enter - for the benefit
of others).

Copied down into E3:E10

HTH
Steve D.

 

[Stephen Dunn]

Aaagh, I left a "magic" number in there. Something I usually remember to
avoid.

Replace:

*2

With:

*COLUMNS($A$1:$B$6)


Steve D.

 

[Harlan Grove]

I'm taking it that you want only the original two lists (in columns A and
B), and one column for the results (column E)...

Use this in E1:

=SMALL($A$1:$B$6,1)

Less typing to use MIN. Also, I'd be lazy and use the OP's rng1 rather than the
literal address of the original data range.

and this in E2:

=IF(ROW()>SUMPRODUCT(($A$1:$B$6<>"")/
COUNTIF($A$1:$B$6,$A$1:$B$6&"")),"",
INDEX(SMALL($A$1:$B$6,ROW(INDIRECT("1:"&ROWS($A$1:$B$6)*2))),
MATCH(0,COUNTIF(E1:E$1,
SMALL($A$1:$B$6,ROW(INDIRECT("1:"&ROWS($A$1:$B$6)*2)))),0)))

...

With =MIN(rng1) in E1, the subsequent formulas can be much, much simpler.

E2:
=SMALL(rng1,1+SUMPRODUCT(COUNTIF($E$1:$E1,rng1)))

Note that E2 is *not* an array formula. Fill this down until it returns #NUM!
errors, then delete the cells containing the errors. If the formulas must trap
errors, then

E1:
=IF(COUNT(rng1),MIN(rng1),"")

E2:
=IF($E1<MAX(rng1),SMALL(rng1,1+SUMPRODUCT(COUNTIF($E$1:$E1,rng1))),"")

Note that E2 is still not an array formula. Again, fill down as needed. The
formulas will evaluate to "" when all distinct entries in rng1 have been
exhausted.

 

[Stephen Dunn]

Thanks Harlan,

I missed the rng1; doh! for the MIN; and, for the rest, I was too busy
trying to turn a known single-column formula into a multi-column one -
couldn't see the wood for the trees...

 

[Sean Bartleet]

Thanks, Steve

Help is really appreciated.

Sean

 

[Sean Bartleet]

Thanks, Harlan

Exactly what I was looking for, pity I cannot get there myself, (yet?).

Sean

...