Stacking Info from rows into Columns

Alex Klingbeil

So i use Smartsheet forms to Have my workers Log what they Completed on a Job Site as a Daily log. In a Day they Can do up to 8 tasks a single day and with the Form its set up to be just a 1 time Fill out to Track everything with out having to do the form 8 times in a row and just doing it once to cover everything.

is there a Formula i can use that will Stack the Info from the Columns in a row?

Above is how the Form sorts the information given by the people. this keeps adding Columns all the way up to 8 tasks. so its a alot of Columns on 1 sheet for something that doesn't need to be. but i also don't want to make the workers fill out something for Each task just 1 Recap at the end of the day that takes very sort

Above is how i would like it to look like. (this is just a Mock up) i would like to have something that does this for me with Automation or something like that.

Hope this what i Rambled on about is Understandable that way someone can help me because i am at a lost on this.

Jgorsich

If you know the order of the columns is fixed, you could use a set of nested if statements to pull it all together onto a helper sheet.

For example, if the hours are all in your 'hours 1" "hours 2" etc columns, you could use something like:

=MAX(COLLECT({YourHours1}, {YourDateColumn}, "the date you want Here",{YourCompanyColumn}, "the company you want Here"), COLLECT({YourHours2}, {YourProjectColumn}, "Project 3"), COLLECT({YourHours3}, {YourProjectColumn}, "Project 3"))

You could use a index(distinct(collect({YourDateColumn}, "the date you want Here",{YourCompanyColumn}, "the company you want Here")),X) as well to cycle through all the different company names for a given date (with a helper column tied to count(distinct(collect())) function to get the number of company names to cycle through if it varies.

You could make it even easier though and just add a few helper columns at the end of the data collection sheet (where all the columns get filled out) that summarizes the data you want, then just run any functions you want on those columns or pull them into a report - then you wouldn't need to collect() nonsense and could just use max() or sum() functions to pull it all together.