How to break down the result into each row?

Peter Vo

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!

Roger_B

Following

Genevieve P.

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