I have a list of names in column A and a list of scores (between 1 and 5) in column B. I want to sort the names out into three categories: scores greater than 4, scores between 3 and 4, and scores less than 3, so a total of 3 formulas. The following formulas copied down their columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

and

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150<3,ROW($B$ 1:$B$150)),ROW(1:1)),1)}

...but I can't make a formula work that returns values between 3 and 4.

Any ideas?

I have a list of names in column A and a list of scores (between 1 and
5) in column B. I want to sort the names out into three categories:
scores greater than 4, scores between 3 and 4, and scores less than 3,
so a total of 3 formulas. The following formulas copied down their
columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

and

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150<3,ROW($B$ 1:$B$150)),ROW(1:1)),1)}

...but I can't make a formula work that returns values between 3 and 4.

Any ideas?

Try specifying...

AND(<4,3)

...as your criteria.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Not sure if I have the syntax correct, but I get #NUM when I try the following:

={INDEX($A$1:$B$150,SMALL(IF(AND($B$1:$B$150<4,$B$ 1:$B$1503),ROW($B$1:$B$150)),ROW(1:1)),1)}

Just curious why you don't just sort the range on Col B?

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi,

Am Wed, 14 Aug 2013 01:41:39 +0100 schrieb sporenta:

I have a list of names in column A and a list of scores (between 1 and
5) in column B. I want to sort the names out into three categories:
scores greater than 4, scores between 3 and 4, and scores less than 3,
so a total of 3 formulas. The following formulas copied down their
columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

try:
=INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=4,ROW($1: $150)),ROW(A1)))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

That works for finding values greater than 4, and when I change it to find values less than 3, it works too. It's finding values in-between 3 and 4 that I still can't get to work. So, working off of your formula I tried:

=INDEX($A$1:$A$150,SMALL(IF(AND($B$1:$B$1503,$B$1 :$B$150<4),ROW($1:$150)),ROW(A1)))

CNTRL+Shft+Enter

But I still get a #NUM error.

Garry, that would be a easy way to go! I'm crunching lots of numbers that are often updated, and this is just the first stop on a longer process. It's easier to have other parts of the workbook look at presorted columns.

Hi,

Am Wed, 14 Aug 2013 13:24:08 +0100 schrieb sporenta:

That works for finding values greater than 4, and when I change it to
find values less than 3, it works too. It's finding values in-between 3
and 4 that I still can't get to work.

filter your column B with number filter "is not equal 3"


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi again,

Am Wed, 14 Aug 2013 15:25:11 +0200 schrieb Claus Busch:

filter your column B with number filter "is not equal 3"

or try:
=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150<3),ROW($ 1:$150)),ROW(A1)))
and enter the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Wed, 14 Aug 2013 15:30:27 +0200 schrieb Claus Busch:

=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150<3),ROW($ 1:$150)),ROW(A1)))

or try:
=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<3)+(B$1:B$150 =4),ROW($1:$150)),ROW(A1)))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

Thanks for the suggestions. That last one is returning non-error results, but too many, unfortunately. When I SHFT+CNTRL+ENTER:

=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<4)+(B$1:B$150 =3),ROW($1:$150)),ROW(A1)))

...it returns all the names, not just the ones that fall between 3 and 4.

Hi,

Am Wed, 14 Aug 2013 18:08:54 +0100 schrieb sporenta:

Thanks for the suggestions. That last one is returning non-error
results, but too many, unfortunately. When I SHFT+CNTRL+ENTER:

=INDEX(A$1:A$150,SMALL(IF((B$1:B$150<4)+(B$1:B$150 =3),ROW($1:$150)),ROW(A1)))

..it returns all the names, not just the ones that fall between 3 and
4.

I thought you want the names <3 and =4.
For the names between 3 and 4 try:
=INDEX($A$1:$A$150,SMALL(IF(($B$1:$B$150=3)-($B$1:$B$1504),ROW($1:$150)),ROW(A1)))
and CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

That did it! Woo-hoo!

Thanks for your help.