ExcelBanter - Concatenate the lookup values from all occurences

On Jun 17, 5:37*pm, JLGWhiz wrote:
I didn't address the concatenate part. *But you would have to do some
manipulation to do the concatenate since your loop is only looking at one
item at a time. *You will need a method to mark each item as it is found and
before starting the next loop, do your concatenate. *If you cannot figure it
out, post back.

"jlclyde" wrote:
I am trying very hard to get Excel to look up multiple occuring values
in column A and return the concatenation of all values in B. *For
instance if I have A1 = 1, A2 = 2 and A3 = 1 *and I ahve B1 = cat, B2
= Dog and B3 = goat. * Based on the value in C1 = 1 it woudl return
cat, goat. *Is this possible. *I am trying to do a for next loop, but
I do not know how to offset it to get the values that I want from
column B.

thanks,
Jay- Hide quoted text -

- Show quoted text -

This is the code that I got to work but then I started thinking that
this woudl make more sense as a Function. I have include the finction
below with the problem that I am having.

Sub POs()
Dim MyRange As Range
Set MyRange = Range("A1:A20")
Dim c As Range
For Each c In MyRange
If c = Range("D1").Value Then
Dim Concat As String
Concat = c.Offset(0, 1) & ", "
Dim D7 As String
D7 = Range("D7").Value
Range("D7") = D7 & Concat
End If
Next
End Sub

Function Concpos(Item As Range, Items As Range)

Dim c As Range
For Each c In Items
If c = Range("D1").Value Then
Dim Concat As String
Concat = c.Offset(0, 1).Value & ", "

End If
Concpos = Concat & Concat
Next
End Function

I am unsure how to keep a variable and then add to it. In the Sub it
was easy to use a cell to store the data, but how is it done in a
function?

Thanks,
Jay