I have a list of parts that are in Kits (like a socket set). It is two
columns A = PARTS and B = KIT. It is sorted by Parts. one part can be in
more than one kit and that is what I need to know.

example:

PART KIT
Part_A Kit_1
Part_A Kit_2
Part_A Kit_3
Part_B Kit_2
Part_B Kit_3
Part_C Kit_1

The above array is in a named range called KIT_DATA. And I was going to use
vlookup untill I realized it would only give me the first kit. The above
data is in Sheet2. Sheet1 lists all of the Parts. The objective is to
insert a comment into the Part on sheet1 and put all the Kits that the part
is in (from sheet2 or KIT_DATA) into the comment.

Soooo....

Sheet1

Part_A would have a comment "Kit_1, Kit_2, Kit_3"
Part_B would have a comment "Kit_2, Kit_3"
Part_C would have a comment "Kit_1"

Does that make sense? Can anyone help? I could loop through the whole mess
but it's about 50,000 lines.

DG

See if this will do what you want.

Sub getKit()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, rng2 As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = sh1.Range("A2:A" & lr1)
Set rng2 = sh2.Range("A2:A" & lr2)
For Each c In rng1
For Each i In rng2
If c.Value = i.Value Then
kt = kt & i.Offset(, 1).Value & vbCrLf
End If
Next
With c.AddComment
.Visible = False
.Text kt
End With
kt = ""
Next
End Sub




"DG" wrote in message
...
I have a list of parts that are in Kits (like a socket set). It is two
columns A = PARTS and B = KIT. It is sorted by Parts. one part can be in
more than one kit and that is what I need to know.

example:

PART KIT
Part_A Kit_1
Part_A Kit_2
Part_A Kit_3
Part_B Kit_2
Part_B Kit_3
Part_C Kit_1

The above array is in a named range called KIT_DATA. And I was going to
use vlookup untill I realized it would only give me the first kit. The
above data is in Sheet2. Sheet1 lists all of the Parts. The objective is
to insert a comment into the Part on sheet1 and put all the Kits that the
part is in (from sheet2 or KIT_DATA) into the comment.

Soooo....

Sheet1

Part_A would have a comment "Kit_1, Kit_2, Kit_3"
Part_B would have a comment "Kit_2, Kit_3"
Part_C would have a comment "Kit_1"

Does that make sense? Can anyone help? I could loop through the whole
mess but it's about 50,000 lines.

DG