Lookup to return multiple data from the same column

[Andy]

Hi,
I'm trying to add a column on a new worksheet that looks up from data from
an existing worksheet, which looks like this:
Company Projects
A. Mole b10
A. Mole b15
A. Mole b13
J. Beam b12
E. Potter b11
E. Potter b14

I would like to be able to find ALL the projects done by "A. Mole" and
return the data into ONE cell.
And so on so that the new sheet would look like this:
Company Project
A. Mole b10, b15, b13
J. Beam b12
E. P otter b11, b14

If anybody can help i'd be extremely grateful.
Cheers Andy

 

[Peo Sjoblom]

Not possible, you would need as many cells as there are projects done
by "A. Mole"

--


Regards,


Peo Sjoblom

 

[Gary''s Student]

Try the following User Defined Function:

Function listum(s As String, tabl As Range) As String
Dim v As String
listum = ""
n = tabl.Rows.Count
For i = 0 To n - 1
v = tabl(1).Offset(i, 0).Value
If v = s Then
listum = listum & "," & tabl(1).Offset(i, 1).Value
End If
Next
listum = Right(listum, Len(listum) - 1)
End Function

With your data in A2 thru B7, the fomula:

=listum("A.Mole",A2:B7)
will return:
b10,b15,b13
all in a single cell


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[Andy]

That was exactly what i was looking for gary thanks. However the example i
gave was simplified. Instead of the data being returned from the 2nd column
i actually need it from the third column. How would i changed the UDF to
suit this?

 

[Andy]

I have just figured out how to change it to suit so thanks very much for all
your help. All i need to do now is to figure out how to get rid of the
"#value!" cells!

 

[Mike H]

A good start to getting the answer to that would be to decide which thread
you are posting in and stick to it or better still don't multiple post at
all.

Mike