Remove blank cell of rows and shift the rest to left

[mehrana]

I have a table with 60+ columns. More data is periodically added (using Microsoft form) to this table. There are / will be many blank cells in each row (80% of the entire cells). Instead of this very wide table, I need to display only the data on a screen without the blank cells. The screen can of any kind, such as another table with the blank cells removed and data cells are shifted to left. It will be great if this can done automatically each time when a new row is added or more data is added to cells (on left side of the existing).

I appreciate any help you can provide.

 

[mehrana]

Try formatting the columns C and D with "dd/mm/yyyy hh:mm"

I did. New format only works on cell outside the range of the table, not inside the table!

 

[felixstraube]

Can you share the file? or a sample of it?

 

[mehrana]

Can you share the file? or a sample of it?

 

[mehrana]

Yes, how can I upload the file here?

 

[felixstraube]

You would need to upload to a cloud storage and share the link here.

 

[mehrana]

You would need to upload to a cloud storage and share the link here.

You would need to upload to a cloud storage and share the link here.

Here is link to the file in Google drive folder:

OnlineForm-1.1.xlsx

docs.google.com

Please let me know if it worked.
As I explained, this is an online file and the data is entered by a Microsoft Form. There are 2 date/time columns, one for when the form was started and the second for when the form was completed. They are built-in to the form and I do not have any access to or option to remove them. I noticed yesterday that the completion date/time showing differently in excel file.

 

[felixstraube]

Oh the cell returns the string instead of the numeric value. That's why.

Here is the corrected formula:

=LET(t, OfficeForms.Table,
tj, BYROW(t, LAMBDA(x, TEXTJOIN("|", 1, x)&"|")),
res, DROP(REDUCE("", tj, LAMBDA(arr,new, VSTACK(arr, TEXTSPLIT(new, "|")))), 1),
IFERROR(IFERROR(VALUE(res), res), "")
)

Let me know if this works for you.

 

[mehrana]

Oh the cell returns the string instead of the numeric value. That's why.

Here is the corrected formula:

=LET(t, OfficeForms.Table,
tj, BYROW(t, LAMBDA(x, TEXTJOIN("|", 1, x)&"|")),
res, DROP(REDUCE("", tj, LAMBDA(arr,new, VSTACK(arr, TEXTSPLIT(new, "|")))), 1),
IFERROR(IFERROR(VALUE(res), res), "")
)

Let me know if this works for you.

Thank you, yes it's working well right now.
Is there anyway I can change format of this view table? like applying table design formats?

 

[felixstraube]

Thanks for the feedback. Happy to help.
You can format it as needed. Only thing you can't do is "Format as table".

But you could copy the format from another table and paste just the format or with the format painter.

 

[mehrana]

Thanks for the feedback. Happy to help.
You can format it as needed. Only thing you can't do is "Format as table".
View attachment 115209

But you could copy the format from another table and paste just the format or with the format painter.

The only issue is I have too select a range for those format, and it does not apply to new rows as they get added to the view table each time a form is submitted.
Another issue is, I added a check box to the main sheet/table, and it shows as TRUE / FALSE in the view form!