Multiple Values Filtered to One Cell

I am fairly new to Smartsheet. I am wanting filter data from one sheet to another capturing multiple values in one cell. I have an associate listing with the hardware they are assigned to. They may have several rows with different hardware. I want to see their hardware in some sort of summary sheet in one field (ex. MacBook Pro, HP AIO). I have a VLOOKUP field working, but it's only pulling in the first row where their name is listed. Am I able to modify this to include the other row data as well?

Best Answer

«1

Answers

  • Kris Mize
    Kris Mize ✭✭✭

    Genevieve, thank you for this! This was exactly what I was looking for. I updated my formula based on what you listed and everything is showing now. Thank you!!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm so glad it worked for you. 🙂

  • Kris Mize
    Kris Mize ✭✭✭

    @Genevieve P - quick follow-up. What you proposed is working perfectly, except that it seems to be limited to 100 rows. I have our associates linked to a number, and when I type in 001 - 099, it pulls the data through correctly. But if I enter 100 - 284, it gives me a #NO MATCH error. Is there a limit? Thank you again for all of your help!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kris Mize

    Could you post a screen capture of the sheets (blocking out data) and posting exactly what your formula is? I'd like to see why it might not be finding a match... how are the numbers 100 - 284 being returned in your sheets?

  • Kris Mize
    Kris Mize ✭✭✭

    Of course! Below is my formula -

    =JOIN(COLLECT({MRK Associate Listing Range 2}, {MRK Associate Listing Range 1}, [Audit Number]290))


  • Genevieve P.
    Genevieve P. Employee Admin

    Hmm interesting. I presume the Audit Number column is the one with the numbers on the very far left? Could you also post a picture of the sheet it's cross-referencing to, the MRK Listing Range 1, with the Audit number?

    My first instinct is to look and ensure that the numbers are actually displaying as numbers, so that there is a match between the sheets. You can usually resolve this by adding in the VALUE function somehow, depending on if it's the source sheet that's displaying oddly or the current sheet.

  • Genevieve P.
    Genevieve P. Employee Admin

    Additionally... would you mind swapping out the row reference "290" with @row?

     [Audit Number]@row


    This will help the sheet to load faster (as it doesn't have to look all the way through the row numbers to see which row you're referencing, but can tell that you just mean within that specific row). Sometimes NOMATCH errors can pop up briefly if the sheet is loading & taking time finding the match.

  • Kris Mize
    Kris Mize ✭✭✭

    So I added the @row, thank you for that suggestion. I'm still getting the #NO MATCH. The 1st screen shot below shows the lookup associate sheet to display the numbers I've assigned to the rows. (of course I've blurred out the data as it's sensitive.). The 2nd screenshot shows my main sheet and how the data pulls through for the original rows, but won't if I attempt to add a row at the end using the same record number as one that works above it. I hope this is making sense and thank you for all of your help with this!


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Kris,

    Thank you, this does make sense. In all honesty I'm not sure why you would be receiving that error on one row when the same value in the row above has a correct match.

    To answer your question about limitations, there’s a limit of 100,000 inbound cells that can be referenced from other sheets into one sheet in total... but I have a feeling your two ranges {in these} do not come close to 100,000 cells.

    At this point I would suggest reaching out to Smartsheet Support. You can provide them with a link to this thread so they have your screen captures. It would also be helpful to know how the numbers on both sheets are input (is a manual entry, or from a form, or through a formula?).

  • Kris Mize
    Kris Mize ✭✭✭

    Thank you for all of your time Genevieve!!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem at all!

  • LGraf
    LGraf ✭✭✭✭

    @Genevieve P. I looked through this thread and hoped you might still be around to help me with a similar =JOIN(COLLECT) challenge. See the attached screenshot. Below is the formula that's in there now, which is returning the #UNPARSEABLE error.

    =JOIN(COLLECT([Contract or Event PO#]1:[Contract or Event PO#]11, [Vendor Name]1:[Vendor Name]11, =[Vendor Name]@row), "- "))

    Very much appreciate any help you can offer.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @LGraf

    It looks like you just have one extra closing parentheses at the end! Try removing that out. You may also want to take out the Row References as well, like so:

    Try this:

    =JOIN(COLLECT([Contract or Event PO#]:[Contract or Event PO#], [Vendor Name]:[Vendor Name], [Vendor Name]@row), " - ")

    Cheers,

    Genevieve

  • LGraf
    LGraf ✭✭✭✭
    edited 07/07/23

    Actually @Genevieve P. , I adapted the formula to my actual sheet and am getting an #UNPARCEABLE error again. I am trying to use data from two sheets, which maybe is causing the problem?

    =JOIN(COLLECT({EC Contract or Event PO#}:{EC Contract or Event PO#}, {EC Vendor Name}:{EC Vendor Name}, [Vendor Name]@row), " - ")

    What is it not liking?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!