Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consecutive occurences | Excel Discussion (Misc queries) | |||
Counting occurences of a name | Excel Worksheet Functions | |||
find number of occurences of text within a column | Excel Worksheet Functions | |||
Count Occurences | Excel Discussion (Misc queries) | |||
Counting Occurences | Excel Discussion (Misc queries) |