=CONCATENATE problem

Hi all

I am trying to use =CONCATENATE to join together a list of names IF they fit a criteria. I don't seem to be able to get it to work with countifs though. I'd appreciate it if anyone is able to help.

Thanks in advance for any help!

Here's what I need to happen


Pseudo code version:
Go down column A and check how many for "Spelling" as a category
If spelling = true, add the name in column B to the string
move to the next instance of spelling in A & loop
Output all of the names of those who need spelling help into a string

Picture version:
PictureVersion.png

KOKOSEK, thanks so much! A really slick answer. I think (hope) all users will be able to use a macro enabled workbook. If not i might come back and ask if we can do it without macros. Your solution saves me a lot of ctrl+ clicking to use concatenate though so I prefer it!

Thanks again

Hi KOKOSEK

All works fine with the macro so thank you indeed for your help.

One final thing if I could? In the cell formula, how can I reference text in a cell? I can't seem to get it to work. Here's your version and what I need in my version. I have included a picture to help explain. I am missing something I think. I have tried the INDIRECT function

Attachment 621183.

=textjoinSUB(IF(A2:A94="Spelling",B2:B94,""),", ",1) (Your version)

=textjoinSUB(IF(A2:A94=C1,B2:B94,""),", ",1) (What I need - C1 needs to pull through the word "spelling" to complete the formula)

whilst you might not have TEXTJOIN you should likely have CONCAT, so a formula based alternative might be:

=REPLACE(CONCAT(INDEX(REPT(","&$B$2:$B$6,$A$2:$A$6=C$1),0)),1,1,"")

above assumes, per your latest post, that C1 contains "Spelling"

if the above returns #NAME? then best to persist with the UDF.

oops! All sorted. Thanks

Never mind. Sorted it... had to use two nested ifs rather than an AND.

Thanks for all your help!