If content in column 1 matches, pull all data into one cell separated by a column

You will need a user-defined function for this.

Hit Alt+F11 and go to Insert|Module... then paste this code in the editor:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then in the spreadsheet in B2 enter formula:

=IF(A2="","",SUBSTITUTE(TRIM(aconcat(IF(samplecontent!$A$2:$A$9=A2,samplecontent!$B$2:$B$9," ")))," ",","))

and confirm it with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula... then copy down

You can put this in the result content worksheet code module (right click the worksheet tab and choose view code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, MyCell As Range, msg As String
If Target.Column <> 1 Then Exit Sub
Set Rng = Sheets("samplecontent").Range("A1:A" & Sheets("samplecontent").Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell = Target.Value Then
msg = msg & "," & MyCell.Offset(0, 1).Value
End If
Next MyCell
On Error GoTo Nxt
Target.Offset(0, 1).Value = Right(msg, Len(msg) - 1)
Nxt:
End Sub

Now when an entry is made in column A of that sheet the result will be filled in!


Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13