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?
|