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

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


"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
>

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
>

On Jun 17, 5:37*pm, JLGWhiz <JLGW...@discussions.microsoft.com> 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

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