MUST BE A WAY!!! Returning Multiple Results Based on a Single Criteria and Combining Them HELP!!1

[kescco]

Okay, I am trying to explain this simply as I can in hopes someone here can help.

In need Excel to search for DOG in Sheet 2 Column A and in Sheet 1 Column B list a combined list of all names it finds in Sheet 2 Column B. An example is as shown below, in Sheet 1 Column B all names I am looking for are listed.

Is this a MATCH, INDEX, CONCATENATE combination? I appreciate you help. I have racked my brains on this one to no avail.


Sheet 1

ANIMAL	TYPES	LIFE
Dog	Collie, Poodle, Labrador	10 Yrs
Cat		8 Yrs
Fish		3 Yrs

<tbody>
</tbody>

Sheet 2

ANIMAL	Common Name	RARE??
Dog	Collie	No
Cat	Siamese	No
Fish	Gold	No
Dog	Poodle	No
Cat	Callico	No
Fish	Guppie	No
Dog	Labrador	No
Cat	Burmese	No
Fish	Tynese Trout	Yes

<tbody>
</tbody>

 

[Peter_SSs]

Try this user-defined function. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MakeList(s As String, r As Range) As String
  Dim a
  Dim rws As Long, i As Long
  Dim t  As String
  
  a = r.Value
  rws = UBound(a, 1)
  For i = 1 To rws
    If a(i, 1) = s Then t = t & ", " & a(i, 2)
  Next i
  MakeList = Mid(t, 3)
End Function

Excel Workbook
	A	B	C
1	ANIMAL	Common Name	RARE??
2	Dog	Collie	No
3	Cat	Siamese	No
4	Fish	Gold	No
5	Dog	Poodle	No
6	Cat	Callico	No
7	Fish	Guppie	No
8	Dog	Labrador	No
9	Cat	Burmese	No
10	Fish	Tynese Trout	Yes
Sheet2

Excel Workbook
	A	B	C
1	ANIMAL	TYPES	LIFE
2	Dog	Collie, Poodle, Labrador	10 Yrs
3	Cat	Siamese, Callico, Burmese	8 Yrs
4	Fish	Gold, Guppie, Tynese Trout	3 Yrs
Sheet1

 

[Rick Rothstein]

Here is a link to a mini-blog article of mine which includes a function that can do what you want...

LookUp Value and Concatenate All Found Results

The function contained in the article gives you the ability to specify the delimiter, restrict the returned concatenation to unique values only (each concatenated item appears only once in the concatenation) and restrict the search to an exact match (by letter casing).

 

[kescco]

i appreciate the help. I am going to try the code now and see how it works.

Please note the tables were just an example, my actual Worksheets are much large with much more detail but if I can get the code correct, I can modify it for my larger sheets.