Return multiple values in one cell doing a single value lookup

[Melody]

Here's the scenario:

A B C D
1 Blue Blue John
2 Yellow Black Mary
3 Black Yellow Carol
4 Yellow Terri
5 Black Joe
6 Blue Tracy


What I need is a formula in Column A that will return all the names in
Column D where Column C matches Column B. So the results would look like
this:

A B C D
1 John, Tracy Blue Blue John
2 Carol, Terri Yellow Black Mary
3 Mary, Joe Black Yellow Carol
4 Yellow Terri
5 Black Joe
6 Blue Tracy

I'm using Excel 2003. This is a small sample, the spreadsheet I'm working
with is much larger. I can't attach the file because it contains
confidential information.

Thanks.

 

[Stefi]

I think that it can be done only with a UDF:

Function findcolor(brng As String) As String
Dim c As Range
With Range("C:C")
Set c = .Find(brng)
If Not c Is Nothing Then
firstAddress = c.Address
Do
result = result & Range("D" & c.Row) & ","
Set c = .Find(brng, c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Len(result) = 0 Then
findcolor = result
Else
findcolor = Left(result, Len(result) - 1)
End If
End Function

Usage (in A2, it's advisable to insert a header row):
=findcolor(B2)

Regards,
Stefi

„Melody†ezt írta:

 

[Melody]

How do you guys learn this stuff? Wow. How do I define the range if C:C is
actually on Sheet 2 Column A and Column D is actually on Sheet 2 Column B?

Thanks,

 

[Melody]

Still waiting on how to adjust where the data is being referenced from.
Also, what needs to be added to not duplicate names in the list. In other
words if John's name in Column D has more than one Black record in Column C
just return John's name once in Column A.

Thanks.

 

[Stefi]

Sorry for the late answer but I've just now realized that my e-mail
notification doesn't work.
1. The best "getting started" is recording macros, reading VBA Help and Edit
recorded macros.
2. Worksheets("Sheet2").Range("C:C")
Worksheets("Sheet2").Range("D" & c.Row)
etc.
3. for eliminating duplicates:

Function findcolor(brng As String) As String
Dim c As Range
With Range("C:C")
Set c = .Find(brng)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If InStr(1, result, Range("D" & c.Row)) = 0 Then _
result = result & Range("D" & c.Row) & ","
Set c = .Find(brng, c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
If Len(result) = 0 Then
findcolor = result
Else
findcolor = Left(result, Len(result) - 1)
End If
End Function

--
Regards!
Stefi



„Melody†ezt írta: