What I am trying to do is be able to lookup one number in Column A (Header is
WP), and give me all the matching results from Column B (Header is PMC), and
put display all the results in Column C. I need to do this without
downloading anymore addins also, thanks for your help in advance. Here is an
example:

WP PMC
1 1 1
2 2 2
3 3 3
4 4 4
5 7 7,8,9,10,52
5 8 7,8,9,10,52
5 9 7,8,9,10,52
5 10 7,8,9,10,52
5 52 7,8,9,10,52

Have you tried using a Pivot Table?
http://peltiertech.com/Excel/Pivots/pivottables.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.contextures.com/xlPivot02.html


Regards,
Ryan---

--
RyGuy


"Curtd" wrote:

What I am trying to do is be able to lookup one number in Column A (Header is
WP), and give me all the matching results from Column B (Header is PMC), and
put display all the results in Column C. I need to do this without
downloading anymore addins also, thanks for your help in advance. Here is an
example:

WP PMC
1 1 1
2 2 2
3 3 3
4 4 4
5 7 7,8,9,10,52
5 8 7,8,9,10,52
5 9 7,8,9,10,52
5 10 7,8,9,10,52
5 52 7,8,9,10,52

I appreciate the idea ryguy but I need it in another cell in a different
column. Any ideas on how to do this?

CD

"ryguy7272" wrote:

Have you tried using a Pivot Table?
http://peltiertech.com/Excel/Pivots/pivottables.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.contextures.com/xlPivot02.html


Regards,
Ryan---

--
RyGuy


"Curtd" wrote:

What I am trying to do is be able to lookup one number in Column A (Header is
WP), and give me all the matching results from Column B (Header is PMC), and
put display all the results in Column C. I need to do this without
downloading anymore addins also, thanks for your help in advance. Here is an
example:

WP PMC
1 1 1
2 2 2
3 3 3
4 4 4
5 7 7,8,9,10,52
5 8 7,8,9,10,52
5 9 7,8,9,10,52
5 10 7,8,9,10,52
5 52 7,8,9,10,52

Ahh! Now I understand the objective. Those commas threw me off last time.
Ok, with €œWP€� in A1 and €œPMC€� in B1, and A2:B10 as such:
1 1
2 2
3 3
4 4
5 7
5 8
5 9
5 10
5 52

Enter this function into D2:
=IF(ROWS(B$2:B2)<=COUNTIF($A$2:$A$20,$C$2),INDEX($ B$2:$B$20,SMALL(IF($A$2:$A$20=$C$2,ROW($A$2:$A$20)-ROW($C$2)+1),ROWS(B$2:B2))),"")

Commit it with Ctrl+Shift+Enter (not just enter) and finally€¦ in C2 place
the value that you want to lookup in Column A.

I put 5 in C2 and got the following in D2:D6:
7
8
9
10
52


Regards,
Ryan--

PS, the arrangement of data is slightly different that what you described;
not sure if you can have that kind of layout...with all the commas between
the values...

--
RyGuy


"Curt D." wrote:

I appreciate the idea ryguy but I need it in another cell in a different
column. Any ideas on how to do this?

CD

"ryguy7272" wrote:

Have you tried using a Pivot Table?
http://peltiertech.com/Excel/Pivots/pivottables.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.contextures.com/xlPivot02.html


Regards,
Ryan---

--
RyGuy


"Curtd" wrote:

What I am trying to do is be able to lookup one number in Column A (Header is
WP), and give me all the matching results from Column B (Header is PMC), and
put display all the results in Column C. I need to do this without
downloading anymore addins also, thanks for your help in advance. Here is an
example:

WP PMC
1 1 1
2 2 2
3 3 3
4 4 4
5 7 7,8,9,10,52
5 8 7,8,9,10,52
5 9 7,8,9,10,52
5 10 7,8,9,10,52
5 52 7,8,9,10,52