collect multiple information from different columns
I have built a formula to collect the information of a row, when a selected row, fits another row.
=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1)); "x"); "")
My problem is that there is the possibility that 2 columns from different rows could fit and i want to have both results. Now the formula only looks if there is a column to collect and only returns 1 information even if it fits to 2 possible information. How can i collect all information that fit and show it in my formula column?
Greetings, Sabrina
Answers
-
Hi @Sabrain
You could add another INDEX(COLLECT function within your JOIN function to bring back another value. However in this instance I would recommend using CHAR(10) as your delimiter:
=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1); CHAR(10)); "x"); "")
So if you're looking for a different value, you'd add in another statement:
=IFERROR(IFERROR(
JOIN(
INDEX(COLLECT(...); 1);
INDEX(COLLECT(...); 1);
INDEX(COLLECT(...); 1);
CHAR(10));
"x"); "")
Does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you for your answer.
I accidentally took a screenshot of the wrong formula at first . The correct formula, which iuse is:
=IFERROR(IFERROR(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1)); "")
I tried to collect multiple results from index(collect) by adding:
=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1));CHAR(10)); "")
but it still does not show me all possible results that match my criteria.
Is it even possible to show multiple results by using index(match)..? ?
Greetings,
Sabrina
-
I want to make sure the right Genevieve gets the credit here. You're amazing, @Genevieve P. !
-
Haha, thanks for the tag and kind words, Genevieve!
Hi @Sabrain,
Perhaps I'm not understanding what it is you're looking to do; it looks like you're using one IFERROR to eliminate a criteria in case there are no matches from the first formula, is that correct?
If that's the case, and what you're asking for is if your initial INDEX formula can return multiple results, then try swapping out INDEX for JOIN:
=JOIN(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); CHAR(10))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives