Require a lookup to return multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top