find occurences

[AJSloss]

I have a column of unique id#s and a column of ratings (where "-" does not
equal a rating) next to them as follows:

ColA ColB
1 819 4
2 820 3
3 821 -
4 822 -
5 823 -
6 824 2
7 825 4
8 826 2
9 827 -
10 828 2

and so on... is there a way that I can put somewhere else on the sheet
(let's say column d) a list of id#s that are rated "2." In this case it
would return 824, 826 and 828 in column d.

 

[Shane Devenshire]

Hi,

Easiest ways are to use AutoFilter to filter in place and if you need copy
these. Next you could use the Advance Filter command to Copy to a new
location. Last you could write a formula.

 

[JB]

http://cjoint.com/?bBuZzpoTwT

Named ranges:
Code =Feuil1!$B$2:$B$11
ID =Feuil1!$A$2:$A$11

F2 contains 2

-Select D211
=IF(ISNUMBER(SMALL(IF(Code=F2,MATCH(ID,ID,0)),ROW(INDIRECT("1:"&ROWS
(ID))))),INDEX(ID,SMALL(IF(Code=F2,MATCH(ID,ID,0)),ROW(INDIRECT
("1:"&ROWS(ID))))),"")
-Valid with Sfift+Ctrl+enter

JB
http://boisgontierjacques.free.fr/

 

[AJSloss]

Thanks,

I was looking for a formula, do you know what that formula would be?

 

[Shane Devenshire]

If you want a formula approach in 2007 you can enter the following

=IFERROR(SMALL(IF((B$1:B$10=E$1)*A$1:A$10<>0,(B$1:B$10=E$1)*A$1:A$10,""),ROW(A1)),"")

Where E1 contains the number 2, or whatever you want to search for.

In 200
=IF(ROW(A1)>COUNTIF(B$1:B$10,E$1),"",(SMALL(IF((B$1:B$10=E$1)*A$1:A$10<>0,(B$1:B$10=E$1)*A$1:A$10,""),ROW(A1))))

Both of these formulas are arrays - which means you press Shift+Ctrl+Enter
to enter them.