Concatenate, Join, and Index

Options

I am looking for a way to pull data from Sheet A into a helper sheet, concatenate and join the data, and then push the result into Sheet B. Sheet A might have 1 parent row and up to 10 children or 1 parent only or any combination thereof. I am having trouble hitting on the right formula without it being a manual process. Any help would be much appreciated.

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Hi,

    some screenshots of your sheet would help. It sounds like you want 3 sheets here? Sheet A, helper sheet, and Sheet B? I wonder why the helper is needed... How many columns are you wanting to concatenate and join? I think just JOIN would accomplish this...

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

  • R Brown
    R Brown ✭✭
    Options

    This is a generic example of Sheet A. I can only pull data from that sheet as I will not be allowed to add any columns, etc. The helper sheet will be used in place of any helper columns on this sheet.

    The end product should look something like this:

    I thought about using an Index/Match expression to pull the data, but I'm having trouble coming up with an expression pull the data into one cell.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @R Brown

    It sounds like what you're looking for would be a JOIN/COLLECT formula. This would join together into one cell all the data that meets a certain criteria. The issue here is that cross-sheet formulas aren't able to identify a Parent in the same way that an in-sheet formula can.

    Normally I would suggest adding a helper column into the source sheet (that you can hide) which identifies the Parent content for each individual row. Then when that's a part of each row, you can use it as criteria to pull over and Join together the Child content from a different column in one cell in your other sheet.

    Is there a possibility that the sheet owner would be able to build this helper column for you? Or is there some other identifier on each row that we can use as the criteria to tell which Solution goes with which Customer?

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!