Search code examples
if-statementgoogle-apps-scriptgoogle-sheetstextjoin

TEXTJOIN cells that only meet the IF

I am working on Google Sheets and have not incorporated Google Script to this question. I have automated a process based on data validation but I wish to make the data look less chunky. My aim is to join the text using a delimiter ", " but ONLY IF they match an exact name and not anyone else's name. See the picture for the example of how I would like it condensed to this;

Ideal table to join text

At the minute I have had to separate each control under a unique column to each of the names but I was wondering if anyone would be able to advise how to use the TEXTJOIN() only if the control reference is next to my name;

Shea Murphy = Join (5.1-b) for In Progress from Column S with all the other names without TEXTJOIN(5.1 etc) as well

Shea Murphy = Join (4.2, 5.10, 8.4) for Completed from Column U with all the other names without TEXTJOIN(8.1, 8.2) as well

Open to the idea of Google Script if it cannot be done on Google Sheets as I wish to improve my skills on that as well. Appreciate any help given :)

Solution

  • Try this for Column D is 'Completed' results & Column E is 'In progress' results

    Completed: =join(",",QUERY(D2:E8,"select E where D ='Shea'"))

    Inprogress: =join(",",QUERY(A2:B8,"select B where A ='Shea'"))

    result