Prev: How to know if a worksheet was unlocked
Next: Removing Special Characters
From: Art on 22 Mar 2010 17:28
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!!!!
From: H�ctor Miguel on 22 Mar 2010 21:09
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!!!!


From: Ashish Mathur on 23 Mar 2010 20:46
Hi,

Download and install the following addin -
http://www.download.com/Morefunc/3000-2077_4-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" <Art(a)discussions.microsoft.com> wrote in message
news:10FA3093-161A-4689-99DB-CB7C546828A3(a)microsoft.com...
> 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!!!!

 | 
Pages: 1
Prev: How to know if a worksheet was unlocked
Next: Removing Special Characters