I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

Assuming your data in A1:B12

In C1:
=SUBSTITUTE(TRIM(MCONCAT(IF(($A$1:$A$12=A1)*($B$1: $B$12<""),$B$1:$B$12,"")&"
"))," ",",")

ctrl+shift+enter, not just enter
copy down


"Curt D." wrote:

I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12

Curt

Have a look at Ashish's site for how to return multiple values from a VLOOKUP

http://office.microsoft.com/en-us/ex...260381033.aspx

May get you on the track you need.


Gord Dibben MS Excel MVP

On Sun, 9 Mar 2008 09:14:02 -0700, Curt D.
wrote:

I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12

I appreciate the help teethless mama, I tried your formula and get "#Name?"
as a result. I don't see what part is causing the problem. Any idea?

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

Assuming your data in A1:B12

In C1:
=SUBSTITUTE(TRIM(MCONCAT(IF(($A$1:$A$12=A1)*($B$1: $B$12<""),$B$1:$B$12,"")&"
"))," ",",")

ctrl+shift+enter, not just enter
copy down


"Curt D." wrote:

I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12

First: Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

then use my formula


"Curt D." wrote:

I appreciate the help teethless mama, I tried your formula and get "#Name?"
as a result. I don't see what part is causing the problem. Any idea?

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

Assuming your data in A1:B12

In C1:
=SUBSTITUTE(TRIM(MCONCAT(IF(($A$1:$A$12=A1)*($B$1: $B$12<""),$B$1:$B$12,"")&"
"))," ",",")

ctrl+shift+enter, not just enter
copy down


"Curt D." wrote:

I have two columns, one (Column A) that repeats and the other (column B) that
has a none repeating number. I need to have a formula that will give me
result in column C that will display all the results from column B when
searching for a number is column A. Like when looking for the number 10 in
Column A, Column B has two numbers (1 & 7) that match that will be displayed
in Colomn C.
Does anyone have an idea on how to do this, I used Vlookup but it will only
give me the first answer. Any help would be appreciated, thanks in advance.
Here is an example:

Column A Column B Column C
10 1 1,7
11 2 2,8
12 3 3,9
13 4 4
14 5 5,11
15 12
10 7 1,7
11 8 2,8
12 9 3,9
13 4
14 11 5,11
15 12 12