Concatenate the lookup values from all occurences

[jlclyde]

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

 

[JLGWhiz]

If you post the code you are using it will facilitate the response.

suppose you are using the For Each method.
Set myRange = Range{"A2:A20")
Dim c As Range
For Each c In myRange
If c = 1 Then
c.Offset(0, 1).Copy '<<is column B same row.
'do something with it
End If
Next

Using For i =

Dim i As Long
For i = 2 To 20
If Cells(i, 1) = 1 Then
Cells(i, 2). Copy '<<column B same row
End If
Next

 

[JLGWhiz]

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]

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.






- 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

 

[jlclyde]

Nevermind. I was over thinking it. below works.

Function Concpos(Item As Range, Items As Range)

Dim c As Range
For Each c In Items

If c = Item Then
Dim Concat As String
Concat = c.Offset(0, 1).Value & ", "
Concpos = Concpos & Concat
End If

Next
End Function

Thanks Jay