Show data from columns that are not empty

Hello everyone

I have a question about a use case and I don't know how to get the result I want in Smartsheet :

I have a sheet with multiple columns (like 100 more or less) and in each column I can have some data or not. I need to have all these columns even if there are empty to store data.

It is very complicated to read because sometime for a specific row I have data in column 1 to 5, and then nothing until column 100.

I can't hide columns because for each row, there is data in different columns so I need to see all of them.

I would like to have a report or a sheet that only give me for each row the information in the columns that are not empty.

The problem is that if I make a report, I need to put all the columns and thus I have the same issue to read my report because it is very long.

Do you have a solution for this use case ?

Here is an example : I would like only to see data from columns that are not empty :

Thank you very much !

Have a nice day,

Corentin

Find more posts tagged with

Accepted answers

All comments

David Joyeuse

Hi @Corentin Dal Farra

Seems what you're looking for is to use a JOIN/COLLECT in an helper Column of all cells that are not blank.

Corentin Dal Farra

Hello @David Joyeuse

With join/collect all the data will be aggregated in one cell is that correct ?

David Joyeuse

@Corentin Dal Farra

Yes.

Something like:

=JOIN(COLLECT({Full Range}, {Full Range}, NOT(ISBLANK(@cell))), CHAR(10))

This works perfectly fine, but I usually don't like doing this, because values are collected and in the end you don't know which value means what in the final cell...

So If I have to do this, I use one helper column for each column that I call Display [Name of the column] and use this in it:

=IF(ISBLANK([Column X]@row), "", "Column X: " + [Column X]@row)

Then I do the JOIN/COLLECT on the whole display Range, instead of columns, and Hide all the display columns.

But In your range that means to create another hundred set of columns...

Hope it helped!

Corentin Dal Farra

Hello @David Joyeuse

Thank you for your help on this subject !

I will try this solution in my use case, but I have already a lot of columns so I am not sure that I will have the possibility to add more columns.

Have a great day,

Corentin

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.