JOIN(COLLECT to locate blank cells

Christos · 07/29/24

Hello and thank you in advance to anyone who responds with suggestions.

GOAL: I have Shipping Tracker sheet (Sh Tkr) and a New Hire Tracker(NHT) sheet and am trying to Join(Collect all shipments ID #s (Sh Tkr - ST ID#) that have not been received into the New Hire Tracker sheet (Open Shipper #'s col). Currently, in the Sh Tkr sheet we manually enter the received date. if not date is entered, it means we have not recieved the shipment.

Working Join(Collect Formula in NHT sheet that lists all shipping #s.

=JOIN(COLLECT({Sh Tkr - ST ID#}, {Sh Tkr - NHT ID#}, [NHT ID]@row), ", ")

Working Join(Collect Formula in NHT sheet that lists Received Dates of Received Shipments (Blanks appear where there is no recieved entry.) Currenty, I receive dates for the received shipment and thought I could modify it to return the dates awaiting shipments.

=JOIN(COLLECT({ST - Recvd Date}, {Sh Tkr - NHT ID#}, [NHT ID]@row), ", ")

I can't seem to get the right combination of arguments to get it to list all ST IT#s that have a blank entry in the Recvd Date col, and have also tried including: ISBLANK, and " ", but no luck.

Hope this makes sense.

jmyzk_cloudsmart_jp · 08/02/24

Hi @Christos

I am having difficulty making a post. So, please check the following doc.

https://docs.google.com/document/d/1ZMe4srSRI9vdWiB5VYEiUfj_OLkwNqb9wtGfavfo77A/edit?usp=sharing

Christos · 08/02/24

Thank you, jmyzk_cloudsmart_jp.

With a little adjustment for my references, your updated formula worked perfectly.
I was trying a similar thing last night with the but could not get the order right.

Thank you very much for your help. I also like your use of CHAR(10) display each return on separate rows. Looks much cleaner and is something I had not tried before.

I am not familiar with Sheet Summary and tried to set it up, but it gave me a message that said copying the sheet will be irreversible. I got concerned and backed out of it. Will try it again with another test sheet.

jmyzk_cloudsmart_jp · 07/30/24

Hi @Christos

You can use NOT(ISDATE(@cell) as the criteria.

=JOIN(COLLECT({Sh Tkr - ST ID#}, {Sh Tkr - Recvd Date}, NOT(ISDATE(@cell ))), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=bce7925e8ac14c159df36eb2a4768aad

You can also use a Sheet Summary field to list the ST ID#s.

https://app.smartsheet.com/b/publish?EQBCT=a1d810d801b8400ebc45f83ac64a8d84

Paul Newcome · 07/30/24

Are you able to provide some screenshots for context?

Christos · 07/31/24

https://community.smartsheet.com/discussion/comment/446918#Comment_446918

I was not able to get it to work using your formula. I received #UNPARSEABLE. I will continue trying. I will also provide screenshots as suggested by Paul Newcome.

Christos · 07/31/24

Hopefully these images help you understand the environment and what I am trying to do. Sorry for the complications, but your help really is appreciated.

I'm actually trying to do two things in the Test HNT sheet.

In Received Shipments col - Show only received Shipment {Sh Tkr - ST ID#} numbers (not dates)
In Open Shipper #'s col show only {Sh Tkr - ST ID#} that have not been received (delivered).

jmyzk_cloudsmart_jp · 08/02/24

Hi @Christos

Here is a revised demo solution.

Test NHT

The new formula includes the NHT ID condition in the COLLECT function.

[Received Shipments] =JOIN(COLLECT({Sh Tkr - ST ID#}, {Sh Tkr - NHT ID#}, [NHT ID]@row, {Sh Tkr - Recvd Date}, ISDATE(@cell)), CHAR(10))

[Open Shipper #s] =JOIN(COLLECT({Sh Tkr - ST ID#}, {Sh Tkr - NHT ID#}, [NHT ID]@row, {Sh Tkr - Recvd Date}, NOT(ISDATE(@cell))), CHAR(10))

Link to Test NHT Sheet

Test ST

You can use the Sheet Summary to show the Received & Open Shippment ST-IDs.

Link to the published Test ST Sheet

jmyzk_cloudsmart_jp · 08/02/24

Hi @Christos

I am having difficulty making a post. So, please check the following doc.

https://docs.google.com/document/d/1ZMe4srSRI9vdWiB5VYEiUfj_OLkwNqb9wtGfavfo77A/edit?usp=sharing

Christos · 08/02/24

Thank you, jmyzk_cloudsmart_jp.

With a little adjustment for my references, your updated formula worked perfectly.
I was trying a similar thing last night with the but could not get the order right.

Thank you very much for your help. I also like your use of CHAR(10) display each return on separate rows. Looks much cleaner and is something I had not tried before.

I am not familiar with Sheet Summary and tried to set it up, but it gave me a message that said copying the sheet will be irreversible. I got concerned and backed out of it. Will try it again with another test sheet.

jmyzk_cloudsmart_jp · 08/03/24

@Christos

I'm happy to help! Questions like yours, with real-world examples, are interesting and motivate me to assist.😁

JOIN(COLLECT to locate blank cells

Best Answers

Answers

Help Article Resources

Categories