How to break down the result into each row?
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

Following

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 overestimate 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!