Thanks! That worked! Although, for some reason, it wouldn't let me reference
the unique list (col E) from a pivot table in another worksheet so I
referenced the unique list using = in the same worksheet and for some reason
that worked(?). I'm just happy it works! I've been working on this on-and-off
for a week.

"T. Valko" wrote:

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then, based on the sample data you posted...

Assuming this data is in the range A2:B12. You have the unique groups listed
in the range E2:E5.

Enter this array formula** in F2 and copy down to F5:

=SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
Yes, I'm definitely interested. I know in reality that it can't be
unlimited,
but I need at least 10 names appended. I'll also have to figure out how to
move anything over 30 characters to the next line, but first things first.

Can this function handle multiple reoccurring group names (i.e. loop
within
a loop)?

group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt

output:

mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt

The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.

"T. Valko" wrote:

There can be...unlimited names for a group.

Well, that's not good! You have to narrow down "unlimited".

There is a free add-in available that has a function that will do this
but
the resulting string is *limited* to no more than 255 characters. So,
that
means "tvalko^debih^biff" can't be more than 255 chars.

Are you interested in this?

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I have reoccuring group names in column A and multiple names (i.e
tvalko,
debih, biff, etc.) in column B for each group. There can be one name or
unlimited names for a group. I want to identify the name s for each
different
group and obtain the list on one line (tvalko^debih^biff) with carats
as
delimeters.

"T. Valko" wrote:

You need to be more specific and provide some details.

--
Biff
Microsoft Excel MVP


"Melanie" wrote in message
...
I'm also trying to do this. I need to lookup a value in one column
that
returns multiple values in the second column, but I want to list it
out
with
"^" between the values. I need to do this dynamically for multiple
lookup
values.

Can you help me?

"Biff" wrote:

You can do that, however, since the formula returns possible
multiple
results for each lookup value you'd have to use another formula
(the
same
one, just change =$A$60 to the next cell reference).

Biff

"Debi H" wrote in message
...
I would like to copy down and do this for all the values in the
list
not
just
the value from A60

"Biff" wrote:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

Use this:

=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?

What do you mean?

Biff

"Debi H" wrote in message
...
One more question please....

If the fromula:
=INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5)

can I change $A$2:$A$15=$A$60 to look up a range?


"Biff" wrote:

You're welcome!

Biff

"Debi H" wrote in message
...
That worked...thanks

"Biff" wrote:

Type the formula then, instead of hitting ENTER like you
normally
would,
hold down both the CTRL key AND the SHIFT key then hit
ENTER.
When
done
properly Excel will enclose the formula in squihhly braces
{ }.
You
can't
just type these braces in, you MUST use the key
combination
to
produce
them.

If you're still having problems I'll be glad to look at
your
file
and
see
if
I can figure it out. Just let me know how to contact you.

Biff

"Debi H" wrote in
message
...
That still does not work for me. Am I missing
something?
I did the key stroke of (CTRL+SHIFT+ENTER) 1ST
Entered the formula
and I get the VALUE error



"Biff" wrote:

Hi!

The basic formula is something like this:

Entered as an array using the key combo of
CTRL,SHIFT,ENTER:

=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))

Then copy down.

Where column A contains the lookup_value and column B
contains
the
values
to
be returned.

Need more specific details to offer a more robust
suggestion.

Biff

"MetricsShiva"
wrote
in
message
...
i want to look up a name that occurs several times in
one
column
of a
spreadsheet and return corresponding values from each
row
the
name
occurs
on.
Vlookup returns only one value. How can I get
multiple
values?