Hi

1. From Cell A1:E1 random numbers from 1 to 13 gets inputed and gets
ranked then put in under A2:E2
i.e. any numbers from 1 to 13 can be inputed into A1,B1,C1,D1 and E1
e.g.if A1 = 2, B1 = 3, C1 = 1, D1 = 5, E1 = 6 then it becomes
A2 = 1, B2 = 2, C2 = 3, D2 = 5, E2 = 6



2. Sometimes Numbers repeat in cells
i.e. A1 = 3, B1 = 3, C1 = 2, D1 = 4, E1 = 3 and as you know already
it will change A2:E2 as below
A2 = 2, B2 = 3, C2 = 3, D2 = 3, E2 = 4


How do I make a only unique values appearing using the formula in
Range A3:E2?
as this A3 = 2, B2 = 3, C2 = 4 because there are only 3 unique numbers

I only know how to achieve this using advanced filter but I can't
really use this since I will have alot of entries later on.

thank you for your help in advance

regards,

James

If I follow correctly then try:

=IF(ISERROR(SMALL(A2:E2,COLUMN())),"",SMALL(A2:E2, COLUMN()))

in A3 and then copy along to E3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"James8309" wrote in message
...
Hi

1. From Cell A1:E1 random numbers from 1 to 13 gets inputed and gets
ranked then put in under A2:E2
i.e. any numbers from 1 to 13 can be inputed into A1,B1,C1,D1 and E1
e.g.if A1 = 2, B1 = 3, C1 = 1, D1 = 5, E1 = 6 then it becomes
A2 = 1, B2 = 2, C2 = 3, D2 = 5, E2 = 6



2. Sometimes Numbers repeat in cells
i.e. A1 = 3, B1 = 3, C1 = 2, D1 = 4, E1 = 3 and as you know already
it will change A2:E2 as below
A2 = 2, B2 = 3, C2 = 3, D2 = 3, E2 = 4


How do I make a only unique values appearing using the formula in
Range A3:E2?
as this A3 = 2, B2 = 3, C2 = 4 because there are only 3 unique numbers

I only know how to achieve this using advanced filter but I can't
really use this since I will have alot of entries later on.

thank you for your help in advance

regards,

James

Hello James,

Select A2:E2 and array-enter:
=TRANSPOSE(Lfreq(A1:E1))

Lfreq is a UDF which you will find he
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

Try this:

Enter this formula in A3:

=MIN(A2:E2)

Enter this array formula in B3 and copy across to E3:

=IF(MIN(IF($A2:$E2A3,$A2:$E2)),MIN(IF($A2:$E2A3, $A2:$E2)),"")

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

--
Biff
Microsoft Excel MVP


"James8309" wrote in message
...
Hi

1. From Cell A1:E1 random numbers from 1 to 13 gets inputed and gets
ranked then put in under A2:E2
i.e. any numbers from 1 to 13 can be inputed into A1,B1,C1,D1 and E1
e.g.if A1 = 2, B1 = 3, C1 = 1, D1 = 5, E1 = 6 then it becomes
A2 = 1, B2 = 2, C2 = 3, D2 = 5, E2 = 6



2. Sometimes Numbers repeat in cells
i.e. A1 = 3, B1 = 3, C1 = 2, D1 = 4, E1 = 3 and as you know already
it will change A2:E2 as below
A2 = 2, B2 = 3, C2 = 3, D2 = 3, E2 = 4


How do I make a only unique values appearing using the formula in
Range A3:E2?
as this A3 = 2, B2 = 3, C2 = 4 because there are only 3 unique numbers

I only know how to achieve this using advanced filter but I can't
really use this since I will have alot of entries later on.

thank you for your help in advance

regards,

James

On Aug 25, 2:35*am, "T. Valko" wrote:
Try this:

Enter this formula in A3:

=MIN(A2:E2)

Enter this array formula in B3 and copy across to E3:

=IF(MIN(IF($A2:$E2A3,$A2:$E2)),MIN(IF($A2:$E2A3, $A2:$E2)),"")

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

--
Biff
Microsoft Excel MVP

"James8309" wrote in message

...



Hi

1. From Cell A1:E1 random numbers from 1 to 13 gets inputed and gets
ranked then put in under A2:E2
i.e. any numbers from 1 to 13 can be inputed into A1,B1,C1,D1 and E1
e.g.if *A1 = 2, B1 = 3, C1 = 1, D1 = 5, E1 = 6 then it becomes
A2 = 1, B2 = 2, C2 = 3, D2 = 5, E2 = 6

2. Sometimes Numbers repeat in cells
i.e. A1 = 3, B1 = 3, C1 = 2, D1 = 4, E1 *= 3 and as you know already
it will change A2:E2 as below
A2 = 2, B2 = 3, C2 = 3, D2 = 3, E2 = 4

How do I make a only unique values appearing using the formula in
Range A3:E2?
as this A3 = 2, B2 = 3, C2 = 4 because there are only 3 unique numbers

I only know how to achieve this using advanced filter but I can't
really use this since I will have alot of entries later on.

thank you for your help in advance

regards,

James- Hide quoted text -

- Show quoted text -

Thanks alot guys!!!!!!!! :D

You're welcome!

--
Biff
Microsoft Excel MVP


"James8309" wrote in message
...
On Aug 25, 2:35 am, "T. Valko" wrote:
Try this:

Enter this formula in A3:

=MIN(A2:E2)

Enter this array formula in B3 and copy across to E3:

=IF(MIN(IF($A2:$E2A3,$A2:$E2)),MIN(IF($A2:$E2A3, $A2:$E2)),"")

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

--
Biff
Microsoft Excel MVP

"James8309" wrote in message

...



Hi

1. From Cell A1:E1 random numbers from 1 to 13 gets inputed and gets
ranked then put in under A2:E2
i.e. any numbers from 1 to 13 can be inputed into A1,B1,C1,D1 and E1
e.g.if A1 = 2, B1 = 3, C1 = 1, D1 = 5, E1 = 6 then it becomes
A2 = 1, B2 = 2, C2 = 3, D2 = 5, E2 = 6

2. Sometimes Numbers repeat in cells
i.e. A1 = 3, B1 = 3, C1 = 2, D1 = 4, E1 = 3 and as you know already
it will change A2:E2 as below
A2 = 2, B2 = 3, C2 = 3, D2 = 3, E2 = 4

How do I make a only unique values appearing using the formula in
Range A3:E2?
as this A3 = 2, B2 = 3, C2 = 4 because there are only 3 unique numbers

I only know how to achieve this using advanced filter but I can't
really use this since I will have alot of entries later on.

thank you for your help in advance

regards,

James- Hide quoted text -

- Show quoted text -

Thanks alot guys!!!!!!!! :D