Join/Collect Column Name and field value

Jennifer Mahoney

Hello Smartsheet folks,

I need to collect all NONblank fields in TEXT columns *including the column name * that contain or start with the formatted date text MM/DD/YYYY and ... also, preferably organize them alphabetically (and/or in this case ...chronologically), example...

below the header row are the columns with the class names and the date/place the student selected to take the class.

I need this, ideally, to appear in the following format...

Fundamentals 101 - 11/07/2022 - Monday - 1:30pm-3:30pm - McCall Hall - Dallas

Specialty 302 - 04/03/2023 - Monday - 2:00pm-5:00pm - Rulan Training Center - Spokane

We can't use actual Date fields for the class date/place, we need to keep it one long string

I've managed to collect them but an stumped as to how to fold in the column name alongside the correct date/place selection.. and once more I'd like to collect based on the contains options as opposed to the "pm" or ":00" value as these might sometimes be only AM classes that end 15 past the hour- so pulling based on the date format, which will always be true, is ideal.

Here is my first stab at it- the collect is just looking at all columns

=JOIN(COLLECT([Class Column1]@row:[Class Column50]@row, [Class Column1]@row:[Class Column50]@row, <>"", [Class Column1]@row:[Class Column50]@row, CONTAINS("pm", @cell), [Class Column1]@row:[Class Column50]@row, CONTAINS(":00", @cell)), CHAR(10))

This pulls every field I need but does not show the corresponding class name and they will be in whatever date order was input from Class Column1 and so on, the dates will not be in the correct date order

thank you in advance!

Jen

Eric Law

Hello @Jennifer Mahoney , Here is a thread that kind of does what you are trying to do.

https://community.smartsheet.com/discussion/84409/return-column-names-and-values-from-row-in-a-reference-column