Combine data from multiple columns, unless data is the same
Hello Smartsheet Community!
We have (2) sets of columns of data that pull in data from (2) other sheets for Ship Dates, Tracking, Order Numbers, etc. Sometimes both sets of columns will be populated sometimes just one column will be populated.
We are currently using the JOIN function to combine the data from a set of (2) columns into a single column, but when both columns are populated, its bunching the data from both columns together (screenshot below of the ship date + tracking columns)
How would we go about asking Smartsheet to look at both columns, bring in the data, but if the data is the same, only bring in one set of the data?
Thank you in advance for the help!
Best Answer

Actually disregard my last comment, I realized I had two of the references backwards. This now works!
=IF([EQP SHIP DATE (ship)]@row = [EQP SHIP DATE (pickup)]@row, [EQP SHIP DATE (ship)]@row, JOIN([EQP SHIP DATE (ship)]@row:[EQP SHIP DATE (pickup)]@row))
Thank you so much @Abdul Tahlil !!!
Answers

Hi @Nancy Heater,
Would adding an if function work?
i.e. ( if(shipdate=pickupdate, shipdate, Join(Shipdate:Pickupdate))
The formula will first check to see if the ship and pickup date are the same. If they are, it will choose one of the two, if not, it will join the two cells.
Hope this helps.
Best.

Hello @Abdul Tahlil !
Thank you for the suggestion. This half way works. Its not not pulling in the data if there is only one column populated (screenshot is below). This is the formula we are using based on your suggestion:
=IF([EQP SHIP DATE (ship)]@row = [EQP SHIP DATE (ship)]@row, [EQP SHIP DATE (pickup)]@row, JOIN([EQP SHIP DATE (ship)]@row:[EQP SHIP DATE (pickup)]@row))

Actually disregard my last comment, I realized I had two of the references backwards. This now works!
=IF([EQP SHIP DATE (ship)]@row = [EQP SHIP DATE (pickup)]@row, [EQP SHIP DATE (ship)]@row, JOIN([EQP SHIP DATE (ship)]@row:[EQP SHIP DATE (pickup)]@row))
Thank you so much @Abdul Tahlil !!!
Help Article Resources
Categories
Check out the Formula Handbook template!