Tech Inventory - Forms Data Manipulation In Google Sheets

Question

I'm trying to set up a school technology inventory spreadsheet using google forms. Here's the spreadsheet.

The header columns are:

A   timestamp       B name      C room

The data columns are:

D Device        E School ID#        F Asset#      G Serial#     H Notes

The form repeats this 10 times (as each class has multiple devices), so

J Device        K School ID#        L Asset#      M Serial#     N Notes
P Device        Q School ID#        R Asset#      S Serial#     T Notes

are also data columns - up to:

BF Device       BG School ID#       BH Asset#     BI Serial#    BJ Notes

We need each device in its own row, with header info in columns A-C and device specific info in rows D-H.

This excellent question and response is nearly exactly what I need, but the formulas seem to derail over the "Notes" data and "dividing by 0". I've been messing with it for a few hours and could use a hand.

Answer 1

You get division by 0 because the formula involves division by $M$1, where you were supposed to put the number of records per form submission. That number is 10 in your case. This is what the formulas should be in your case.

In A2:

=OFFSET(responses!$A$2, ceiling((row()-row($A$1))/10) - 1, 0, 1, 3) 

This picks the headers from responses sheet. 1, 3 means to pick 1 by 3 range. The row offsets is computed from the current row number, so we move down once every 10 rows. The column offset is zero.

In D2:

=OFFSET(responses!$D$2, ceiling((row()-row($A$1))/10)-1, mod(row()-(row($A$1)+1), 10)*6, 1, 5)

This picks a 1 by 5 range (since there are 5 fields to fetch). The row offset is the same as in the previous formula. The column offset is mod(row()-(row($A$1)+1), 10)*6 which means we move to the right by 6 positions every time, returning to initial position every 10 rows.

Note that the table will have a lot of essentially blank rows since the additional devices on the form are optional. To remove them, you can use built-in filtering (filter by nonempty values on D) or put the formula

=filter(Sheet3!A:H, len(Sheet3!D:D))

on yet another sheet, and use that sheet.