Combine text from multiple cells if other cell has a value of 1

Closed
shenton Posts 1 Registration date Thursday October 29, 2015 Status Member Last seen October 29, 2015 - Oct 29, 2015 at 11:03 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Oct 29, 2015 at 12:20 PM
As per the picture I have a spreadsheet that tracks what courses an individual has completed and if that course has a Certification attached to it. If a Training Participant has a value of "1" under a Course Name it means they completed that course and if that course has a Certification attached to it I need to have the name of the Certification (in text, with commas between each Certification) in a cell at the end of that Individuals Row. Can anyone help me with a formula to do this?
NOTE: There will be as many as 300 Individuals completing as many as 75 different courses and not all courses have certifications attached to them.

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 29, 2015 at 12:20 PM
Hi Shenton,

Try the following formula:
=IF(B2=1,$B$1&", ","")&IF(C2=1,$C$1&", ","")&IF(D2=1,$D$1&", ","")

There is going to be a comma space (, ) at the end of the string.
To remove that use the following formula:
=LEFT(F2,LEN(F2)-2)


Or combine the 2 by replacing both F2 references from the second formula with the first formula.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0