Values from one column against all the occurances from 2nd column

[fari1]

Hi there,
i need to have a formula for my below mentioned problem,

i've in one column names and in 2nd column numbers in the below format

Andes                2
matthews           3
abdes                1
farmi                 1
pholy                 3

what i want is to get all the occurances from column 1 against one particular value from column 2
say i want to get all the values from column 1 against "3" from column 2
which is

Matthews
Pholy

hope i didn't mess it up.
Thanking u in advance

 

[Aladin Akyurek]

Hi there,
i need to have a formula for my below mentioned problem,

i've in one column names and in 2nd column numbers in the below format

Andes                2
matthews           3
abdes                1
farmi                 1
pholy                 3

what i want is to get all the occurances from column 1 against one particular value from column 2
say i want to get all the values from column 1 against "3" from column 2
which is

Matthews
Pholy

hope i didn't mess it up.
Thanking u in advance

<TABLE style="WIDTH: 234pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=313><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1564" width=44><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2332" width=66><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=75>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2882992 class=xl63 width=44>Score</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=66>Score</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Andes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>matthews</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>abdes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>List</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>farmi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>matthews</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>pholy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2882992 class=xl64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>pholy</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR></TBODY></TABLE>

A1:B6 houses the sample you posted, the headers included.

E2: 3 (A score of interest.)

E3, just enter:

COUNTIF(B2:B6,E2)

E4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$5:E5)<=$E$3,INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$E$2,
   ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($E$5:E5))),"")

 

[fari1]

Hi,Thanks for help,E5 is getting data from where? isn't it the result of the formula? i didn't get it, sorry will u plz elaborate?

 

[Aladin Akyurek]

Hi,Thanks for help,E5 is getting data from where? isn't it the result of the formula? i didn't get it, sorry will u plz elaborate?

E5 has the formula you need to copy down after confirming it with control+shift+enter.

Re-cap...

E1: Score

E2: 3

E3, just enter:

=COUNTIF(B2:B6,E2)

E4: List

E5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$5:E5)<=$E$3,INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$E$2,
  ROW($B$2:$B$6)-ROW($B$2)+1),ROWS($E$5:E5))),"")

 

[T. Valko]

Hi there,
i need to have a formula for my below mentioned problem,

i've in one column names and in 2nd column numbers in the below format

Andes                2
matthews           3
abdes                1
farmi                 1
pholy                 3

what i want is to get all the occurances from column 1 against one particular value from column 2
say i want to get all the values from column 1 against "3" from column 2
which is

Matthews
Pholy

hope i didn't mess it up.
Thanking u in advance

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[fari1]

Hi Aladin, arrays make my sheet crazy, since the data in my original workbook is large, so using it along too many rows will make my file very heavy, i surfed over the in internet and got this formula

=IF(ROWS(A$33:A33)<=A$31,INDEX(Sheet1!$A$2:$A$50,SMALL(IF(Sheet1!$C$2:$C$50=A$30,ROW(Sheet1!$A$2:$A$50)-ROW($A$2)+1),ROWS(A$33:A33))),"")

it works like a charm, the only problem is this,instead of a blank at the end of it, i want to use #N/A, becuase the data will be used in chart, and plotting blanks in the chart creates problems, the only solution is #N/A, but when i replace "" with #N/A in above formula, it gives errors, can u help me on this plz, it would be greatly appreciated, or change your formula without arrays

 

[Aladin Akyurek]

Hi Aladin, arrays make my sheet crazy, since the data in my original workbook is large, so using it along too many rows will make my file very heavy, i surfed over the in internet and got this formula

=IF(ROWS(A$33:A33)<=A$31,INDEX(Sheet1!$A$2:$A$50,SMALL(IF(Sheet1!$C$2:$C$50=A$30,ROW(Sheet1!$A$2:$A$50)-ROW($A$2)+1),ROWS(A$33:A33))),"")

it works like a charm, the only problem is this,instead of a blank at the end of it, i want to use #N/A, becuase the data will be used in chart, and plotting blanks in the chart creates problems, the only solution is #N/A, but when i replace "" with #N/A in above formula, it gives errors, can u help me on this plz, it would be greatly appreciated, or change your formula without arrays

This is exactly the same formula as the one I posted (for E5)...

Assuming that A31 houses a CountIf formula, try...

What do you have in A31, and also A30?

 

[fari1]

yes, its working, it has to be array, thanks alot for your great work, thanks u so much, its working

 

[Aladin Akyurek]

yes, its working, it has to be array, thanks alot for your great work, thanks u so much, its working

You are welcome. Thanks for providing feedback.