Combine Cells with Unique Identifier
I have a sheet that allows users to scan certain components online
They may scan the same tracking number, but different components online at several different times in the process. Is there a way to combine all of the same tracking numbers to reflect the online status of each component? Example row #1 the glass came online, row #2 the wood came online. (same tracking number) I would like to have a record that reflects both glass and wood online in the same row so I could do an index/match to the tracking # on another sheet.
Currently my index/match only picks up the first row for that tracking number. It shows online for the glass, but not the wood since the glass is the first record for that tracking #. Hoping there would be a way to use collect and a helper sheet to combine the tracking number into one record that would reflect glass and wood online in one row....
Thank you!
Best Answer
-
Will the values only ever by "ONLINE" or blank? I'm wondering if instead of using an Index(Match, if we could use a COUNTIFS cross sheet formula, and if the count is 0 return blank or if it's 1 return "ONLINE".
So, first we would do this:
COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE")
And then embed that in an IF statement, like so:
=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE") = 0, "", "ONLINE")
You would need to create a new formula for each column reference, like so:
=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Wood Column}, "ONLINE") = 0, "", "ONLINE")
Let me know if this makes sense or if you'd like to see screen captures!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Will the values only ever by "ONLINE" or blank? I'm wondering if instead of using an Index(Match, if we could use a COUNTIFS cross sheet formula, and if the count is 0 return blank or if it's 1 return "ONLINE".
So, first we would do this:
COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE")
And then embed that in an IF statement, like so:
=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Glass Column}, "ONLINE") = 0, "", "ONLINE")
You would need to create a new formula for each column reference, like so:
=IF(COUNTIFS({Tracking Number Column}, [TRACKING #]@row, {Wood Column}, "ONLINE") = 0, "", "ONLINE")
Let me know if this makes sense or if you'd like to see screen captures!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!