How to break down the result into each row?

Options
Peter Vo
Peter Vo ✭✭✭✭
edited 06/05/23 in Formulas and Functions

good morning guys,

I have used the formula to extract the name of the trading partners as below. There is 3 of them ( Caterpillar, Tesla and John Deere. However, all of them are in one row. is it possible to split them into each rows as below? Thanks!


Answers

  • Roger_B
    Roger_B ✭✭✭✭
    Options
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Peter Vo

    If you need the results parsed out down multiple rows, I would recommend using a Row Report instead of a sheet with formulas. That way as new results are added to the main sheet the Report will automatically update with new data as well, in separate rows.

    The way to do this with a formula would require you to know the number of values that will be returned at any given time. If you're not sure, you would need to over-estimate the number of rows.

    Then you would need one column that lists out the number of rows you want to bring back. 1, 2, 3, etc in individual cells.

    Once you have that, instead of JOIN distinct collect you would use INDEX distinct collect. At the very end of the INDEX function there's a spot to indicate what row you're bringing back... the first match? The second match? This is where we'll reference your numerical column:

    =INDEX(DISTINCT(COLLECT({TrueAuto ALL Teams Range 18}, {TrueAuto ALL Teams Range 4}, "Aqua Prieta")), [Helper Column]@row)

    Here's another thread where I used screen captures to explain.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!