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
-
Hi Kris,
It sounds like a JOIN(COLLECT formula might be better for you, as this will "join" together all of the different cells that match your criteria, and bring it into one cell. Is that what you're looking to do?
Try something like this:
=JOIN(COLLECT({Column with Hardware in other sheet}, {Column with associate in other sheet}, "Associate Name"), ",")
This is using two different Cross Sheet References. The first column listed is the one you want the values from. The second column listed has the criteria, and the criteria is after it.
Then, at the end you specify how you want the values to be separated. I've use a comma "," but you could use a dash "-" or something else.
If you have the names listed in your second sheet with the formula, you can simplify this even more by using a cell reference as the criteria instead of typing it in quotes:
=JOIN(COLLECT({Column with Hardware in other sheet}, {Column with associate in other sheet}, [Associate Name]@row), ",")
Then you can drag-fill this formula down and it will automatically update to have the correct data just for the associate in that current row (since we used the @row function).
Let me know if this works! If not, it would be useful to see screen captures of both sheets, but please block out any sensitive data.
Cheers,
Genevieve
Answers
-
Hi Kris,
It sounds like a JOIN(COLLECT formula might be better for you, as this will "join" together all of the different cells that match your criteria, and bring it into one cell. Is that what you're looking to do?
Try something like this:
=JOIN(COLLECT({Column with Hardware in other sheet}, {Column with associate in other sheet}, "Associate Name"), ",")
This is using two different Cross Sheet References. The first column listed is the one you want the values from. The second column listed has the criteria, and the criteria is after it.
Then, at the end you specify how you want the values to be separated. I've use a comma "," but you could use a dash "-" or something else.
If you have the names listed in your second sheet with the formula, you can simplify this even more by using a cell reference as the criteria instead of typing it in quotes:
=JOIN(COLLECT({Column with Hardware in other sheet}, {Column with associate in other sheet}, [Associate Name]@row), ",")
Then you can drag-fill this formula down and it will automatically update to have the correct data just for the associate in that current row (since we used the @row function).
Let me know if this works! If not, it would be useful to see screen captures of both sheets, but please block out any sensitive data.
Cheers,
Genevieve
-
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!!
-
Wonderful! I'm so glad it worked for you. 🙂
-
@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!
-
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?
-
Of course! Below is my formula -
=JOIN(COLLECT({MRK Associate Listing Range 2}, {MRK Associate Listing Range 1}, [Audit Number]290))
-
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.
-
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.
-
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!
-
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?).
-
Thank you for all of your time Genevieve!!
-
No problem at all!
-
@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.
-
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
-
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
Categories
Check out the Formula Handbook template!
