Concatenating 2 or more data points

Hi Folks - not even sure how to describe this without visuals, so i'm posting a dummy sheet with an example. I'm working with school data, where a student may have failed 2 or more subjects. I want to have those subjects in the same cell, separated by a comma so that i can blend it into a mail-merge document. This way, each student will only be listed once, as opposed to what you see in the attached sheet. ID is the student, and the course listed is what they've failed.

I'm not sure Excel can do this, but i thought i'd ask. The more info i can give to our counselors, the quicker we can see more kids. Thanks for any help you can offer, even if it's a "no-way!"

Marylea

currently use Glenn Kennedy moderator custom VBA formula C2 cell , array formula , drag down

HTML Code: 

Originally Posted by MrShorty

I'm not sure what video you watched or where you get the impression that TEXTJOIN() only works for horizontal data. I don't have an Excel version, but LO Calc has not trouble with vertical data in the TEXTJOIN() function or the array IF() function. Unless I am misunderstanding something, it looks like either approach works, so you can choose which approach you like.

Wow - sorry - i see that now. Thank you!

{=CONCATY(IF(1,B2:B4,""))}

OR

{=CONCATY(IF(B2:B4<>"",B2:B4,""))}


Customization can be used with worksheets. I also wrote VBA. The TEXTJOIN function is exactly the same as OFFICE2019 and MS365. Even if everyone has OFFICE2019 and MS365, my TEXTJOIN can only use the old version below 2019

Originally Posted by MrShorty

I'm not sure what video you watched or where you get the impression that TEXTJOIN() only works for horizontal data. I don't have an Excel version, but LO Calc has not trouble with vertical data in the TEXTJOIN() function or the array IF() function. Unless I am misunderstanding something, it looks like either approach works, so you can choose which approach you like.

Looking at your original TEXTJOIN function, i see that you give just one ID code . . . is there a way to ping off of several without having to enter each textjoin command? I have over 1000 kids on this list to concatenate.

Originally Posted by MrShorty

Enter each unique student id (some of Excel's filter tools have an option to return a list of unique entries from a range if you don't want to enter each student id separately). Make sure the formula has the right combination of relative and absolute references and copy/paste/fill the formula down as far as your unique list of students goes.
If your Excel version supports it, there is the new UNIQUE() function.
This tutorial describes how to use Advanced filter to create a list of unique entries . . .

OK, thank you. I will study this and try for it. I had to simplify today, as i was out of time, but i need to learn this function for the future. Thanks again.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.