collect multiple information from different columns

Sabrain
Sabrain ✭✭✭
edited 03/10/23 in Smartsheet Basics

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Sabrain
    Sabrain ✭✭✭
    edited 03/15/23

    @Genevieve Evans

    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. !

  • Genevieve P.
    Genevieve P. Employee Admin

    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))