Concatenate by group excluding current row

[maelstromm]

I am trying to good way to concatenate items that are grouped by a column but excludes the item in the current row. The only reference I could find to a similar task was here. But this is a fairly manual method and I wonder if there is an easier way to do this with a macro especially with a data set that is 8000 rows and has 300 groups. Here is a sample of what I am trying to do:

[TABLE="width: 380"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Group[/TD]
[TD]Related[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Group 1[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Group 1[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Group 2[/TD]
[TD]Item 4, Item 5[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD]Group 2[/TD]
[TD]Item 3, Item 5[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD]Group 2[/TD]
[TD]Item 3. Item 4[/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD]Group 3[/TD]
[TD]Item 7, Item 8, Item 9, Item 10[/TD]
[/TR]
[TR]
[TD]Item 7[/TD]
[TD]Group 3[/TD]
[TD]Item 6, Item 8, Item 9, Item 10[/TD]
[/TR]
[TR]
[TD]Item 8[/TD]
[TD]Group 3[/TD]
[TD]Item 6, Item 7, Item 9, Item 10[/TD]
[/TR]
[TR]
[TD]Item 9[/TD]
[TD]Group 3[/TD]
[TD]Item 6, Item 7, Item 8, Item 10[/TD]
[/TR]
[TR]
[TD]Item 10[/TD]
[TD]Group 3[/TD]
[TD]Item 6, Item 7, Item 8, Item 9[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for your help!!

 

[njimack]

This UDF should do the trick.
Formula in C2: =Related(A2,B2,$B$2:$B$11)
Copy down as far as required

Function Related(Item As String, Group As String, Groups As Range)
Dim c As Range
For Each c In Groups
    If c = Group And c.Offset(, -1) <> Item Then Related = Related & c.Offset(, -1) & ", "
Next c
Related = Left(Related, Len(Related) - 2)
End Function

 

[InzieBear]

I presume....that you are trying to do all items relating to the Group they are in? Is this correct?

 

[maelstromm]

That is correct. Thank you for the quick response. I will give it a shot and let you know!

 

[maelstromm]

Works like a charm. Thank you InzieBear!!