Option Explicit
Sub GetcolumnHeaders()
Dim fAddr As String
Dim sStr As String, s As String
Dim rng As Range
Dim rng2 As Range
Dim cell As Range
Dim cell1 As Range
With Worksheets("Sheet1")
Set rng = .Range(.Range("A2"), _
.Range("A2").End(xlDown))
End With
For Each cell In rng
Set rng2 = Nothing
sStr = cell.Value
With Worksheets("sheet2").Cells
Set rng = .Find( _
What:=sStr, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
If rng2 Is Nothing Then
Set rng2 = rng
Else
Set rng2 = Application.Union(rng2, rng)
End If
Set rng = .FindNext(rng)
Loop While rng.Address < fAddr
End If
cell.Offset(0, 1).ClearContents
s = ""
If Not rng2 Is Nothing Then

For Each cell1 In rng2
s = s & _
cell1.Parent.Cells(1, _
cell1.Column) & ","
Next
cell.Offset(0, 1).Value = Left(s, Len(s) - 1)
End If
End With
Next

End Sub

--
Regards,
Tom Ogilvy


"Complete Newb" wrote:

It's weird that every time I think I'm going to know how to do something, it
winds up I get 1/4 or 1/2 way there and then stuck.

On Sheet1 I have one column of text values (about 500) in Column A (w/
header of "Unique"). On Sheet2 there are many columns and many rows, with
all kinds of values everywhere. What I need to do is:

Run through Sheet2, and for every occurrence of a value in Sheet1's "Unique"
column, enter the column heading(s) of the column(s) that value is in in
Column 2 of Sheet1 next to that value.

For instance, Sheet1's "Unique" column has:

Gear
Tranny
Door

On Sheet2:

- The value "Gear" is located in A5 (Column heading of "Parts"), E114
(Column heading of "Labor"), and G55 (Column heading of "Misc").
- The value "Tranny" is in B45 (Column heading of "Traps")
- The value "Door" is in A88 (Column heading of "Parts") and E6 (Column
heading of "Labor").

So, after I run a sub procedure, Sheet1 should show the following:

Gear Parts,Labor,Misc
Tranny Traps
Door Parts, Labor

I don't care if it's easier to put each column header instance in a separate
column on Sheet1 (instead of values separated by commas), because I can
combine them as a separate step. Also, if getting the actual column header
value is a big complication, I don't mind just returning the column letters
and then I can run a sub or use a formula to change column letters to their
respective header names as a separate step also.

Can anyone help me figure out how to do this?

Any help is greatly appreciated, and thanks for reading.