list of unique elements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Helena - 12 Feb 2008 20:55 GMT

Hello,

How can I get by formula, in a column, the list of unique elements in a
matrix of x columns x rows.
For example:
A1: A5 ={1,2,3,4,5}
B1: B5 = {0,4,5,1,0}
C1: C5 = {5,4,0,4,0}
Desired outcomes in
E1: E15 = {2,3,"","","","","","","","","","","","",""}

Thank you in advance for the help that you will help me.
Helena

Reply to this Message

Bob Phillips - 12 Feb 2008 21:49 GMT

If used as a 5x3 block-array formula, this returns the array but not in that
order

=IF(COUNTIF($A$1:$C$5,$A$1:$C$5)=1,$A$1:$C$5,"")

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
>
[quoted text clipped - 9 lines]
> Thank you in advance for the help that you will help me.
> Helena

Reply to this Message

Helena - 13 Feb 2008 12:47 GMT

Thanks Bob,

I consider this solution to ac ...
But, i opted for the proposal from Biff who responded perfectly to the
original question.

Helena

> If used as a 5x3 block-array formula, this returns the array but not in
> that order
[quoted text clipped - 14 lines]
>> Thank you in advance for the help that you will help me.
>> Helena

Reply to this Message

T. Valko - 12 Feb 2008 22:25 GMT

This array formula** will extract the values (if any) in ascending order:

Entered in E1 and copied down to E15.

rng = A1:C5

=IF(ROWS(E$1:E1)<=SUM(--(COUNTIF(rng,rng)=1)),SMALL(IF(COUNTIF(rng,rng)=1,rng),ROWS(E$1:E1)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Signature

Biff
Microsoft Excel MVP

> Hello,
>
[quoted text clipped - 9 lines]
> Thank you in advance for the help that you will help me.
> Helena

Reply to this Message

Helena - 13 Feb 2008 12:42 GMT

Hi Biff,

Just perfect !
Congratulations, and thank you for your help.

Helena

> This array formula** will extract the values (if any) in ascending order:
>
[quoted text clipped - 20 lines]
>> Thank you in advance for the help that you will help me.
>> Helena

Reply to this Message

T. Valko - 13 Feb 2008 18:41 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Hi Biff,
>
[quoted text clipped - 27 lines]
>>> Thank you in advance for the help that you will help me.
>>> Helena

Reply to this Message