Require a lookup to return multiple values

Hi,

I am trying to get a lookup to return multiple text values.

My data looks like this:
Mike Bob Sue
Chicago x x
Cleveland x x
New York x
Miami x

and I want to summarize the data in another spreadsheet in 1 cell like:
Sue's Trips, Chicago, Miami.

I could do this in multiple columns but I want to avoid rows. I also hope
to avoid pivot tables because of the way my data is stored.

I have tried lookups and array formula's:

This array formulae is the closest I can get and will work to add numbers,
but it will not work for text cells. (returns only first instance or nothing)
=SUM(IF($F$59:$F$63="x",$E$59:$E$63,""))

Please help! :-)

There isn't anything builtin that will do that. You can download Laurent
Longre's morefunc addin and it has a function or two that would do it I
believe.

http://longre.free.fr/english/

It has a help file that explains all the functions. You want one that will
contcatenate text strings in an array formula.

--
Regards,
Tom Ogilvy



"Matt" <Matt@discussions.microsoft.com> wrote in message
news:14309DBE-EF9E-4D31-BFD2-C5A03DA4450D@microsoft.com...
> Hi,
>
> I am trying to get a lookup to return multiple text values.
>
> My data looks like this:
> Mike Bob Sue
> Chicago x x
> Cleveland x x
> New York x
> Miami x
>
> and I want to summarize the data in another spreadsheet in 1 cell like:
> Sue's Trips, Chicago, Miami.
>
> I could do this in multiple columns but I want to avoid rows. I also hope
> to avoid pivot tables because of the way my data is stored.
>
> I have tried lookups and array formula's:
>
> This array formulae is the closest I can get and will work to add numbers,
> but it will not work for text cells. (returns only first instance or
nothing)
> =SUM(IF($F$59:$F$63="x",$E$59:$E$63,""))
>
> Please help! :-)

Thanks for all the help... through all your posts and some heavy research on
my part I came up with the solution I was looking for. For those of you who
are in the same boat here is my solution:

I borrowed the following code from http://www.mcgimpsey.com/ and entered it
in a VB module.

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs: rRng - range to be concatenated
' sDelimiter - optional delimiter
' to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


Once in there, it allows me to use my array formula like the one below
perfectly. Where B6:B8 is my range containing my tags, and A6:A8 is the range
containing my text descriptions.

=(IF(B6:B8="x",MultiCat(A6:A8," ,"),""))

God love Mother Internet!!!!!

Thanks again,
Matt









"Tom Ogilvy" wrote:

> There isn't anything builtin that will do that. You can download Laurent
> Longre's morefunc addin and it has a function or two that would do it I
> believe.
>
> http://longre.free.fr/english/
>
> It has a help file that explains all the functions. You want one that will
> contcatenate text strings in an array formula.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:14309DBE-EF9E-4D31-BFD2-C5A03DA4450D@microsoft.com...
> > Hi,
> >
> > I am trying to get a lookup to return multiple text values.
> >
> > My data looks like this:
> > Mike Bob Sue
> > Chicago x x
> > Cleveland x x
> > New York x
> > Miami x
> >
> > and I want to summarize the data in another spreadsheet in 1 cell like:
> > Sue's Trips, Chicago, Miami.
> >
> > I could do this in multiple columns but I want to avoid rows. I also hope
> > to avoid pivot tables because of the way my data is stored.
> >
> > I have tried lookups and array formula's:
> >
> > This array formulae is the closest I can get and will work to add numbers,
> > but it will not work for text cells. (returns only first instance or
> nothing)
> > =SUM(IF($F$59:$F$63="x",$E$59:$E$63,""))
> >
> > Please help! :-)
>
>
>