If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!!!! |
#2
|
|||
|
|||
find all instances of a search item and put result in a single cel
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!!!! |
#3
|
|||
|
|||
find all instances of a search item and put result in a single
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!!!! . |
#4
|
|||
|
|||
find all instances of a search item and put result in a single
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? |
#5
|
|||
|
|||
find all instances of a search item and put result in a single
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? . |
#6
|
|||
|
|||
find all instances of a search item and put result in a single cel
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!!!! |
Thread Tools | |
Display Modes | |
|
|