trying to concatinate based on values matching in certain cells

[chunt]

I have the following info:


A_________________B________C________D______

Sarah Schwartz A1 1163233
Anne Schwartz A2 1163233
Marcus Lehr A2 1582167
Eric Lehr A1 1582167
Matthew Weiss A2 2194223
Kari Weiss A1 2194223
Ryan Mc Cartney A1 2299079
Kelly Mc Cartney A2 2299079
Cornelius Brendan-Guiney A1 2301508
Roberta Guiney A2 2301508
Nancy Trojan A3 2302575
Kenneth Trojan A2 2302575
Debra Wittington A1 2302575
Shannon Kozlowski A2 2348145
Shannon Kozlowski A1 2348145
Jesse Zoller A1 2383021
Gary Zoller A3 2383021
Jeremy Zoller A2 2383021


I need to concatenate a string in column E such that for all matching
numbers in column D (they will be above or below eachother as it is
sorted by column D) I get all of the names in Column A separated by
the & symbol.

for example, I need to end up with a string that looks like Nancy
Trojan&Kenneth Trojan&Debra Wittington for the value 2302575 in column
D or Shannon Kozlowski&Shannon Kozlowski for the value 2348145 in
column D.

Thanks for any help you can give.

 

[Billy Liddel]

I'm not sure that I have read your details properly but this worked on the
data I copied.

=IF(AND(OFFSET(C2,-1,0)=C2,OFFSET(C2,1,0)<>C2),A1&",
"&A2,IF(OFFSET(C2,1,0)=C2,"",A2))

Hope this works I entered it in D2 & copied down.

Regards
peter

 

[Shane Devenshire]

Hi,

You can't reaonable do this with a formula because you are not limited to
two matchs, you might have 50. You would need to use VBA.

 

[Dave Peterson]

And that concatenated string should only show up on the first row of the group?

If yes:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStr As String
Dim TopRow As Long

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

TopRow = FirstRow

'prime the pump with the first entry
myStr = .Cells(TopRow, "A").Value

'avoid the first row and use the last row +1 to get the last group
For iRow = FirstRow + 1 To LastRow + 1
If .Cells(iRow, "D").Value = .Cells(TopRow, "D").Value Then
myStr = myStr & "&" & .Cells(iRow, "A").Value
Else
.Cells(TopRow, "E").Value = myStr
TopRow = iRow
myStr = .Cells(TopRow, "A").Value
End If
Next iRow
End With

End Sub

 

[chunt]

That worked perfectly. Thanks.

And that concatenated string should only show up on the first row of the group?

If yes:

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim iRow As Long
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim myStr As String    
    Dim TopRow As Long

    Set wks = Worksheets("Sheet1")

    With wks
        FirstRow = 1 'no headers???
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

        TopRow = FirstRow

        'prime the pump with the first entry
        myStr = .Cells(TopRow, "A").Value

        'avoid the first row and use the last row +1 to get the last group
        For iRow = FirstRow + 1 To LastRow + 1
            If .Cells(iRow, "D").Value = .Cells(TopRow, "D").Value Then
                myStr = myStr & "&" & .Cells(iRow, "A")..Value
            Else
                .Cells(TopRow, "E").Value = myStr
                TopRow = iRow
                myStr = .Cells(TopRow, "A").Value
            End If
        Next iRow
    End With

End Sub










--

Dave Peterson- Hide quoted text -

- Show quoted text -