find occurences
 

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.

find occurences
 

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AJSloss" wrote:

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.

find occurences
 

http://cjoint.com/?bBuZzpoTwT

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

F2 contains 2

-Select D2:D11
=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/

On 25 jan, 20:30, AJSloss wrote:
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.

find occurences
 

Thanks,

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

"Shane Devenshire" wrote:

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AJSloss" wrote:

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.

find occurences
 

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 2003
=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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AJSloss" wrote:

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.