concat rows after doing multiple table joins in a Google Sheet

Question

I have created a minimal example sheet at https://docs.google.com/spreadsheets/d/1nrPMDTKD0uHbWkAu-3c9DUoxBptB13lScOe8XI8zxF4/edit?usp=sharing.

I will explain:

• issues and recommendations is a list of issues with their recommendations
  • Each issue has 1+ recommendation in one cell; for example: B2 has alpha and charlie
  • A recommendation could apply to multiple issues; for example: recommendation alpha applies to issue 1, 2, and 5
• issues and recommendations split - I took the data in issues and recommendations and split it so each recommendation was in one line
• recommendations is a unique list of just the recommendations. And I assigned each recommendation a unique ID.
• recommendation plans - each recommendation has 1+ plan on this sheet. Each plan is on it's own line.

Now, in issues and recommendations.plans (column C) I want an ARRAYFORMULA or something that will find all of the recommendation plans.plans (column C) for the recommendations in issues and recommendations.recommendation (column B), and combine them into one cell.

In the last column of issues and recommendations I put an example column with the expected output. Using issue 1 as an example:

• two recommendations:

  • alpha has recommendations ID of 1 that has these plans:
    • do this
    • do that
  • charlie has recommendation ID of 1 that has these plans:
    • do 001
    • do 002
    • do bingo

• so if you combine them you get:

  - do this
  - do that
  - do 001
  - do 002
  - do bingo
  

Answer 1

all you need to do is:

=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(REGEXREPLACE(TRIM(FLATTEN(QUERY(
 TRANSPOSE(IFERROR(VLOOKUP(SPLIT(B2:B, CHAR(10)), 
 TRIM(SPLIT(FLATTEN(TRIM(QUERY(QUERY({"- "&'recommendation plans'!B2:B&"♦", 
 "- "&'recommendation plans'!C2:C&"♥♥", ROW('recommendation plans'!A2:A)}, 
 "select max(Col2) where Col1 <> '- ♦' group by Col3 pivot Col1"),,9^9))), 
 "♦")), 2, 0))),,9^9))), "♥♥$", ), "♥ -", "♥-"), "♥", CHAR(10)))

---

=ARRAYFORMULA(
    SUBSTITUTE(
        SUBSTITUTE(
            REGEXREPLACE(
                TRIM(
                    FLATTEN(
                        QUERY(
                            TRANSPOSE(
                                IFERROR(
                                    VLOOKUP(
                                        SPLIT(
                                            B2:B,
                                            CHAR(10)
                                        ),
                                        TRIM(
                                            SPLIT(
                                                FLATTEN(
                                                    TRIM(
                                                        QUERY(
                                                            QUERY(
                                                                {
                                                                    "- " & 'recommendation plans'!B2:B & "♦",
                                                                    "- " & 'recommendation plans'!C2:C & "♥♥",
                                                                    ROW('recommendation plans'!A2:A)
                                                                },
                                                                "select max(Col2) where Col1 <> '- ♦' group by Col3 pivot Col1"
                                                            ),
                                                            ,
                                                            9^9
                                                        )
                                                    )
                                                ),
                                                "♦"
                                            )
                                        ),
                                        2,
                                        0
                                    )
                                )
                            ),
                            ,
                            9^9
                        )
                    )
                ),
                "♥♥$",
            ),
            "♥ -",
            "♥-"
        ),
        "♥",
        CHAR(10)
    )
)