Transpose Webform Results

Dave B

We are collecting quantity and item descriptions via webform for internal supply requests.

 

Each row is a request.  

 

The first several columns in the row are the requester's information (Name, Dept., etc.).

 

All other columns in the row are corresponding quantity and item description "pairs", such as, "Stapler Qty", "Stapler Description", where the description is a drop-down box of available items in that category.

 

I would like an automated way to transpose these columns into rows on a new sheet so they might read like this:

 

John Doe

Accounting

2 Stapler

3 Pencil

5 Keyboard

 

Sally Travers

Human Resources

3 Legal Pad

10 Blue Ink Pen

 

Instead of like this:

 

John Doe Accounting 2 Stapler 3 Pencil 5 Keyboard

Sally Travers Human Resources 3 Legal Pads 10 Blue Ink Pens 

 

Any slick ideas?

 

Thanks!

 

Jim Hook

Unlike Excel, Smartsheet does not have a transpose function in either linking or copy/paste. I've run into this issue several times and the only way I've found around this is to make my own transpose table on either the source or destination sheet. On the source sheet, for example, the transpose table takes items in a row and using simple formulas converts them into a vertical list which is then linked into the other sheet. It's not ideal but it works.

Dave B

Jim,

 

Thanks for the reply.   I've thought of that, but as the source sheet grows when data is submitted, how are you getting around having to manually add new formulas to accommodate the new row?

 

Thanks for added detail.

 

Dave

 

Jim Hook

I haven't had to cope with exactly your issue but here's something that might work if you have a reasonable maximum number of webform submissions, say 100. Set your sheet up so the webform submissions came in at the top of the sheet. Then, create 100 blank rows at the top of the sheet knowing that the submissions will come in above them. Next, below the 100 blank rows create a transpose group of rows using absolute cell references to the first 100 rows in the sheet, one group for each submission expected. Each of these groups then links out to the sheet that shows the transposed data. It would be tedious setting up the absolute cell references this way especially if you expect hundreds or thousands of submissions.

Dave B

I tried doing that, sort of.  I create absolute cell references with the incoming added to the top of the sheet - $column$1.   When the data came in the "asolute" references shifted with the addition of the new row and now read $column$2.   Back to the drawing board!  Thanks for your help though.

Jim Hook

You're right. If you insert a row above the cell referenced by the absolute reference it will adjust the reference. Incoming webform rows must act like inserting a row at the top.

Travis

I just tested this with Zapier and it worked for me!

 

I created a multi-step Zap which is triggered by a new row being added (such as from a web form). When the new row is added, the first step will add the first column data to the first column of a new row on the second sheet. Then the next step will take the data from the second column and add it to the first column of the next row on the second sheet sheet, and so on. Each step will add the next columns data to a new row in the second sheet.

 

Check it out: https://zapier.com

Dave B

Travis, Thanks will check that out!