|
|
|
date: Sun, 24 Aug 2008 04:23:57 -0700 (PDT),
group: microsoft.public.excel.worksheet.functions
back
Excluding Repeating Numbers Help!1
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
date: Sun, 24 Aug 2008 04:23:57 -0700 (PDT)
author: James8309
Re: Excluding Repeating Numbers Help!1
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
sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"James8309" wrote in message
news:0a88089a-b37e-4388-8d5d-7a88ffd23d64@l33g2000pri.googlegroups.com...
> 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
>
date: Sun, 24 Aug 2008 12:46:27 +0100
author: Sandy Mann
Re: Excluding Repeating Numbers Help!1
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:$E2>A3,$A2:$E2)),MIN(IF($A2:$E2>A3,$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
news:0a88089a-b37e-4388-8d5d-7a88ffd23d64@l33g2000pri.googlegroups.com...
> 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
date: Sun, 24 Aug 2008 12:35:20 -0400
author: T. Valko
Re: Excluding Repeating Numbers Help!1
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:$E2>A3,$A2:$E2)),MIN(IF($A2:$E2>A3,$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
>
> news:0a88089a-b37e-4388-8d5d-7a88ffd23d64@l33g2000pri.googlegroups.com...
>
>
>
> > 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
date: Sun, 24 Aug 2008 16:13:36 -0700 (PDT)
author: James8309
Re: Excluding Repeating Numbers Help!1
You're welcome!
--
Biff
Microsoft Excel MVP
"James8309" wrote in message
news:437b12cb-9a4d-46bb-a0f7-6f3ffa525896@v39g2000pro.googlegroups.com...
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:$E2>A3,$A2:$E2)),MIN(IF($A2:$E2>A3,$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
>
> news:0a88089a-b37e-4388-8d5d-7a88ffd23d64@l33g2000pri.googlegroups.com...
>
>
>
> > 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
date: Sun, 24 Aug 2008 21:18:28 -0400
author: T. Valko
|
|
