I am creating a course development spreadsheet. One sheet has a list of all
of the course developers and their contact information. A second sheet lists
all of the courses being developed.

I would to have a column in the first sheet (list of developers) that shows
which courses they have developed over time. For example, there is a column
in the second sheet (courses) where we identify in Column A the course ID
(e.g., BUS 280) and in Column K we identify the developer (e.g., Mary Smith).

In the second sheet, I'd like Column H to search the second sheet and find
every instance of courses, say, Mary Smith developed over time. So, the cell
in Column H might say

--BUS 280 or
--BUS 280, BUS 356, BUS 348 or
--some other combination

Is this possible? I know how to search for a single instance and return that
value using VLOOKUP, but I am not sure if

(1) you can search for multiple instances and
(2) can return all of those instances in some legible format (e.g.,
separated with a comma) in ONE cell

Thanks!!! You are all awesome with you clear and quick replies!!!

Art
I think you have a typo in what you wrote. I think you want column H of
the FIRST sheet, not the SECOND sheet, to display that listing of all the
courses done by each developer. On the second sheet you have the course
listings in Column A and the developer in Column K. Is that right? I'll
work up a macro for you. HTH Otto

"Art" wrote in message
...
I am creating a course development spreadsheet. One sheet has a list of
all
of the course developers and their contact information. A second sheet
lists
all of the courses being developed.

I would to have a column in the first sheet (list of developers) that
shows
which courses they have developed over time. For example, there is a
column
in the second sheet (courses) where we identify in Column A the course ID
(e.g., BUS 280) and in Column K we identify the developer (e.g., Mary
Smith).

In the second sheet, I'd like Column H to search the second sheet and find
every instance of courses, say, Mary Smith developed over time. So, the
cell
in Column H might say

--BUS 280 or
--BUS 280, BUS 356, BUS 348 or
--some other combination

Is this possible? I know how to search for a single instance and return
that
value using VLOOKUP, but I am not sure if

(1) you can search for multiple instances and
(2) can return all of those instances in some legible format (e.g.,
separated with a comma) in ONE cell

Thanks!!! You are all awesome with you clear and quick replies!!!

Art
This macro does what you want. I assumed your first sheet is named
"First" and your second sheet is named "Second". As written this macro
assumes Column H of the first sheet is blank from row 2 down. Make changes
in the code as needed. Beware of line wrapping in this email. View it in
full screen and copy/paste this macro in full screen. If you wish, send me
an email and I'll send you the small workbook I developed for this. Or,
again if you wish, send me your workbook (fake the data as you need to, I
need just the layout) and I'll place this macro where needed (with necessary
changes) and I'll place a button on the first sheet with which to run this
macro. My email is . Remove the "extra" in
this email address. HTH Otto
Sub CoursesDevpt()
Dim rFirstColA As Range, rSecColK As Range
Dim i As Range, rVisible As Range
Dim Dest As Range, rFilter As Range
Dim j As Range, TheStr As String
Sheets("First").Select
With Sheets("Second")
Set rFirstColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Range("H2")
Set rSecColK = .Range("K2", .Range("K" & Rows.Count).End(xlUp))
Set rFilter = .Range("A1", .Range("A" &
Rows.Count).End(xlUp)).Resize(, 11)
For Each i In rFirstColA
If Not rSecColK.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing
Then
rFilter.AutoFilter Field:=11, Criteria1:=i.Value
TheStr = ""
For Each j In rSecColK.SpecialCells(xlCellTypeVisible)
If TheStr = "" Then
TheStr = j.Offset(, -10).Value
Else
TheStr = TheStr & ", " & j.Offset(, -10).Value
End If
Next j
rFilter.AutoFilter
Dest = TheStr
Set Dest = Dest.Offset(1)
Else
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub

"Art" wrote in message
...
I am creating a course development spreadsheet. One sheet has a list of
all
of the course developers and their contact information. A second sheet
lists
all of the courses being developed.

I would to have a column in the first sheet (list of developers) that
shows
which courses they have developed over time. For example, there is a
column
in the second sheet (courses) where we identify in Column A the course ID
(e.g., BUS 280) and in Column K we identify the developer (e.g., Mary
Smith).

In the second sheet, I'd like Column H to search the second sheet and find
every instance of courses, say, Mary Smith developed over time. So, the
cell
in Column H might say

--BUS 280 or
--BUS 280, BUS 356, BUS 348 or
--some other combination

Is this possible? I know how to search for a single instance and return
that
value using VLOOKUP, but I am not sure if

(1) you can search for multiple instances and
(2) can return all of those instances in some legible format (e.g.,
separated with a comma) in ONE cell

Thanks!!! You are all awesome with you clear and quick replies!!!

You're incredible! I'll give it a try tomorrow and let you know if I have any
questions.

"Otto Moehrbach" wrote:

Art
This macro does what you want. I assumed your first sheet is named
"First" and your second sheet is named "Second". As written this macro
assumes Column H of the first sheet is blank from row 2 down. Make changes
in the code as needed. Beware of line wrapping in this email. View it in
full screen and copy/paste this macro in full screen. If you wish, send me
an email and I'll send you the small workbook I developed for this. Or,
again if you wish, send me your workbook (fake the data as you need to, I
need just the layout) and I'll place this macro where needed (with necessary
changes) and I'll place a button on the first sheet with which to run this
macro. My email is . Remove the "extra" in
this email address. HTH Otto
Sub CoursesDevpt()
Dim rFirstColA As Range, rSecColK As Range
Dim i As Range, rVisible As Range
Dim Dest As Range, rFilter As Range
Dim j As Range, TheStr As String
Sheets("First").Select
With Sheets("Second")
Set rFirstColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set Dest = Range("H2")
Set rSecColK = .Range("K2", .Range("K" & Rows.Count).End(xlUp))
Set rFilter = .Range("A1", .Range("A" &
Rows.Count).End(xlUp)).Resize(, 11)
For Each i In rFirstColA
If Not rSecColK.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing
Then
rFilter.AutoFilter Field:=11, Criteria1:=i.Value
TheStr = ""
For Each j In rSecColK.SpecialCells(xlCellTypeVisible)
If TheStr = "" Then
TheStr = j.Offset(, -10).Value
Else
TheStr = TheStr & ", " & j.Offset(, -10).Value
End If
Next j
rFilter.AutoFilter
Dest = TheStr
Set Dest = Dest.Offset(1)
Else
Set Dest = Dest.Offset(1)
End If
Next i
End With
End Sub

"Art" wrote in message
...
I am creating a course development spreadsheet. One sheet has a list of
all
of the course developers and their contact information. A second sheet
lists
all of the courses being developed.

I would to have a column in the first sheet (list of developers) that
shows
which courses they have developed over time. For example, there is a
column
in the second sheet (courses) where we identify in Column A the course ID
(e.g., BUS 280) and in Column K we identify the developer (e.g., Mary
Smith).

In the second sheet, I'd like Column H to search the second sheet and find
every instance of courses, say, Mary Smith developed over time. So, the
cell
in Column H might say

--BUS 280 or
--BUS 280, BUS 356, BUS 348 or
--some other combination

Is this possible? I know how to search for a single instance and return
that
value using VLOOKUP, but I am not sure if

(1) you can search for multiple instances and
(2) can return all of those instances in some legible format (e.g.,
separated with a comma) in ONE cell

Thanks!!! You are all awesome with you clear and quick replies!!!


.