find all instances of a search item and put result in a single cel

Is it possible to search a column in one sheet for all occurrences of a
search item and then put the result of that search in a single cell on a
second sheet?

For example, Sheet 1 may have something like this:

A :: B
John yellow
Mary yellow
Sue red
Richard green
Michael yellow

In the second sheet, I have a row for each color, and I want to put in, say,
H1, the names in Sheet 1 that have that color in column B.

C :: H
yellow John, Mary, Michael

So, say I have "yellow" in B1, I want the spreadsheet to put in H1 the name
of each person in Column A, separated with a comma and space, in Sheet 1 who
has that color in Column B.

It's possible there may be NO occurences, ONE occurence, or MORE THAN ONE
occurence, which makes it even more difficult because I only need
commas/spaces for MORE THAN ONE occurence.

Thanks!!!!

hi, Art !

does a udf serves well ? (i.e.)

Function ConcatenateIF(cond As String, comp As Range, conc As Range, _
Optional sep As String = ", ", _
Optional match As Boolean = False, _
Optional skip_blanks As Boolean = False) As String
Dim criteria As Range, n As Integer, match1 As Boolean, tmp As String
tmp = ""
For Each criteria In comp
n = n + 1
match1 = IIf(match, criteria = cond, LCase(criteria) = LCase(cond))
If skip_blanks Then match1 = match1 And Not IsEmpty(conc.Cells(n))
If match1 Then tmp = tmp & IIf(Len(tmp), sep, "") & conc.Cells(n)
Next
ConcatenateIF = tmp
End Function

use it as any integrated ws.function (i.e.)

- sheet 2:
[B1] yellow
[H1] =ConcatenateIF(b1,sheet1!b2:b6,sheet1!a2:a6)

hth,
hector.

__ OP __
Is it possible to search a column in one sheet for all occurrences of a search item
and then put the result of that search in a single cell on a second sheet?
For example, Sheet 1 may have something like this:
A :: B
John yellow
Mary yellow
Sue red
Richard green
Michael yellow
In the second sheet, I have a row for each color, and I want to put in, say, H1
the names in Sheet 1 that have that color in column B.
C :: H
yellow John, Mary, Michael
So, say I have "yellow" in B1, I want the spreadsheet to put in H1 the name of each person in Column A
separated with a comma and space, in Sheet 1 who has that color in Column B.
It's possible there may be NO occurences, ONE occurence, or MORE THAN ONE occurence
which makes it even more difficult because I only need commas/spaces for MORE THAN ONE occurence.
Thanks!!!!

Looks a great solution! However, I couldn't get it to work.

My spreadsheet is actually more complex than names and colors. But, its
basically the same concept. Sheet1 (actually called Development) has a list
of course being developed at my school over the past 3 years. Sheet2
(actually called Developers) is a list of people hired to develop the
courses.

I want to find all instances in Sheet1 of each developer (Column D) in
Sheet1 (Column K) and then show results (course IDs in Column C of Sheet1)
and put in Column H of Sheet2).

I think your solution should work. So, I copied the function you provided,
inserted a new module in the VBA editor, and pasted it. Then in the first
cell in Column H of Sheet2, I pasted

=ConcatenateIF(D39,Development!$K$5:$K$94,Developm ent!$C$5:$C$94)

I filled 10 rows with that formula, but every cell shows #NAME?

D39 is the name of the developer (I started ranomly on row 39.)
Development!$K$5:$K$94 is the column with developers' names
Development!$C$5:$C$94 is the column with course IDs

The list of courses developed/being developed in Sheet1 starts in row 5 and
goes through row 94.

The names are exact matches, if there is a match. (Actually, the user
chooser developers' names on Sheet1 from a drop-down box, which is generated
from the list of developers on Sheet2.)

Any thoughts? Am I missing something?





"Héctor Miguel" wrote:

hi, Art !

does a udf serves well ? (i.e.)

Function ConcatenateIF(cond As String, comp As Range, conc As Range, _
Optional sep As String = ", ", _
Optional match As Boolean = False, _
Optional skip_blanks As Boolean = False) As String
Dim criteria As Range, n As Integer, match1 As Boolean, tmp As String
tmp = ""
For Each criteria In comp
n = n + 1
match1 = IIf(match, criteria = cond, LCase(criteria) = LCase(cond))
If skip_blanks Then match1 = match1 And Not IsEmpty(conc.Cells(n))
If match1 Then tmp = tmp & IIf(Len(tmp), sep, "") & conc.Cells(n)
Next
ConcatenateIF = tmp
End Function

use it as any integrated ws.function (i.e.)

- sheet 2:
[B1] yellow
[H1] =ConcatenateIF(b1,sheet1!b2:b6,sheet1!a2:a6)

hth,
hector.

__ OP __
Is it possible to search a column in one sheet for all occurrences of a search item
and then put the result of that search in a single cell on a second sheet?
For example, Sheet 1 may have something like this:
A :: B
John yellow
Mary yellow
Sue red
Richard green
Michael yellow
In the second sheet, I have a row for each color, and I want to put in, say, H1
the names in Sheet 1 that have that color in column B.
C :: H
yellow John, Mary, Michael
So, say I have "yellow" in B1, I want the spreadsheet to put in H1 the name of each person in Column A
separated with a comma and space, in Sheet 1 who has that color in Column B.
It's possible there may be NO occurences, ONE occurence, or MORE THAN ONE occurence
which makes it even more difficult because I only need commas/spaces for MORE THAN ONE occurence.
Thanks!!!!


.

hi, Art !

if you get the error value #NAME? this means excel is not recognizing the udf, so (perhaps)...
you pasted the code in a "class" code-module (i.e in ThisWorkbook or any sheet-code-module)
instead of creating/adding a (new ?) "standard-code-module" in vba editor (?)

can you please confirm/correct/... this situation ?
hth,
hector.

__ OP __
Looks a great solution! However, I couldn't get it to work.

My spreadsheet is actually more complex than names and colors.
But, its basically the same concept.
Sheet1 (actually called Development) has a list of course being developed at my school over the past 3 years.
Sheet2 (actually called Developers) is a list of people hired to develop the courses.

I want to find all instances in Sheet1 of each developer (Column D) in Sheet1 (Column K)
and then show results (course IDs in Column C of Sheet1) and put in Column H of Sheet2).

I think your solution should work. So, I copied the function you provided
inserted a new module in the VBA editor, and pasted it.
Then in the first cell in Column H of Sheet2, I pasted

=ConcatenateIF(D39,Development!$K$5:$K$94,Developm ent!$C$5:$C$94)

I filled 10 rows with that formula, but every cell shows #NAME?

D39 is the name of the developer (I started ranomly on row 39.)
Development!$K$5:$K$94 is the column with developers' names
Development!$C$5:$C$94 is the column with course IDs

The list of courses developed/being developed in Sheet1 starts in row 5 and goes through row 94.

The names are exact matches, if there is a match.
(Actually, the user chooser developers' names on Sheet1 from a drop-down box
which is generated from the list of developers on Sheet2.)

Any thoughts? Am I missing something?

I think I just hadn't enabled macros when I worked on this at home. When I
enabled macros, it worked fine!

Thanks so much!!!!





"Héctor Miguel" wrote:

hi, Art !

if you get the error value #NAME? this means excel is not recognizing the udf, so (perhaps)...
you pasted the code in a "class" code-module (i.e in ThisWorkbook or any sheet-code-module)
instead of creating/adding a (new ?) "standard-code-module" in vba editor (?)

can you please confirm/correct/... this situation ?
hth,
hector.

__ OP __
Looks a great solution! However, I couldn't get it to work.

My spreadsheet is actually more complex than names and colors.
But, its basically the same concept.
Sheet1 (actually called Development) has a list of course being developed at my school over the past 3 years.
Sheet2 (actually called Developers) is a list of people hired to develop the courses.

I want to find all instances in Sheet1 of each developer (Column D) in Sheet1 (Column K)
and then show results (course IDs in Column C of Sheet1) and put in Column H of Sheet2).

I think your solution should work. So, I copied the function you provided
inserted a new module in the VBA editor, and pasted it.
Then in the first cell in Column H of Sheet2, I pasted

=ConcatenateIF(D39,Development!$K$5:$K$94,Developm ent!$C$5:$C$94)

I filled 10 rows with that formula, but every cell shows #NAME?

D39 is the name of the developer (I started ranomly on row 39.)
Development!$K$5:$K$94 is the column with developers' names
Development!$C$5:$C$94 is the column with course IDs

The list of courses developed/being developed in Sheet1 starts in row 5 and goes through row 94.

The names are exact matches, if there is a match.
(Actually, the user chooser developers' names on Sheet1 from a drop-down box
which is generated from the list of developers on Sheet2.)

Any thoughts? Am I missing something?


.

Hi,

Download and install the following addin -
http://www.download.com/Morefunc/300...-10423159.html. Then array
enter (Ctrl+Shift+Enter) the following formula

=SUBSTITUTE(TRIM(MCONCAT(IF($C$4:$C$8=B14,$B$4:$B$ 8," ")," "))," ",", ")

B14 has yellow. C4:C8 has the colours and B4:B8 has the names

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Art" wrote in message
...
Is it possible to search a column in one sheet for all occurrences of a
search item and then put the result of that search in a single cell on a
second sheet?

For example, Sheet 1 may have something like this:

A :: B
John yellow
Mary yellow
Sue red
Richard green
Michael yellow

In the second sheet, I have a row for each color, and I want to put in,
say,
H1, the names in Sheet 1 that have that color in column B.

C :: H
yellow John, Mary, Michael

So, say I have "yellow" in B1, I want the spreadsheet to put in H1 the
name
of each person in Column A, separated with a comma and space, in Sheet 1
who
has that color in Column B.

It's possible there may be NO occurences, ONE occurence, or MORE THAN ONE
occurence, which makes it even more difficult because I only need
commas/spaces for MORE THAN ONE occurence.

Thanks!!!!