Vlookup for Multiple Cells in Single Cell? 2

[AppStaff]

I'm sure I can probably do this but not sure how. Can anyone suggest a solution?

I have a table that looks like this

1,bill,Design
1,sally,test
1,tom,test
2,frank,design

I'd like to return to a cell all names from the second column that have the number 1 in the first column and test in the third column.

I know you can return a single name by using vlookup but is there a way to use vlookup or some other function to return multiple values ie in the example "sallyTom"?
Ideally this would be formatted like "sally, Tom"

Thanks for any advice!

 

[kpetursson]

One solution would be a macro, but this would not be automatic like a formula.



Kevin Petursson

 

[dhulbert]

You would need to do it in code but you could create your own function so you use it like a formula.

 

[GlennUK]

If you add-in the MOREFUNC add-in from Laurent Longre (

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

), and use MCONCAT combined with extra formulae in a new column, that would work.

For example, assuming your data is in columns A, B, and C, then have another column of formulae like this:

=IF(A2=1,IF(C2="test",B2&",",""),"")

copied down as far as necessary.

Then in the cell where you want the results concatentated use this formula ( using one of the new added-in functions ):

=LEFT(MCONCAT(D14),LEN(MCONCAT(D14))-1)

Good luck.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AppStaff]

Thanks Glenn, star for your suggestion.

 

[GlennUK]

my pleasure

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Just for the record you could also have done this very easily with:-

Data / Filter / Autofilter - Probably the easiest way

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

Data / Filter / Advanced Filter

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

Pivot Table

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

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

Thanks Ken, those methods are very good for showing the required matches as a list ... but concatenating the results is most easily done with MCONCAT.

There are a lot of useful things in Laurents free add-in actually ... I must explore them more thoroughly.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Must admit I always just use a function (Pinched from JE McGimpsey):-

Public Function MultiCat(ByRef rRng As Excel.Range, _
Optional ByVal sDelimiter As String = "") As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelimiter & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, 1 + Len(sDelimiter))

'Call as =MultiCat(A1:A5)
'you can add a delimiter if you wish:
' =MultiCat(A1:A5,",")

End Function

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

Ah yes Ken, very nice ... but most of my contacts are scared to death of touching VBA by themselves. Yours has almost the same functionality as MCONCAT except that yours doesn't skip empty cells. The beauty of doing something like that yourself, of course, is that you can tweak the functionality to suit whatever purpose is required ... for example skipping empty and null strings in cells.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[AppStaff]

Thanks Ken. That function will help me as well. Star awarded to you also.

 

[KenWright]

You're very welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------