Search code examples
jquerygoogle-sheetsoffsettranspose

OFSSET + transpose

I'm trying to reduce the amount of documents e have and one of the tasks is to have all information fed by a form and then Google Sheets to sort it.

At the moment I have that all the responses are in one row for every product. However, I would like it to be duplicated 5 times, once for each possible food component wasted. Ive used these 2 formulas in A2 and K2 in the HOLDING V! tab:

=OFFSET(responses!$A$2,ceiling((row()-1)/5,1)-1,column()-1)

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

Even tho it does use the 3 columns I want, it does not write the component 2, 3, 4 and 5 and its respective KG and Holding. I wonder if there is a way of doing that a I've tried different ways of formulating that.

The final table should look something like this:

From column K to M on the HOLDING tab:

stew base / 4 / Charity

frozen sweetcorn / 4 / Charity

frozen sweetcorn / 4 /Charity

""" / "" / ""

"" / "" / ""

test / 6 / Charity

testing5 / 4 / Charity

frozen sweetcorn / 4 / Charity

olives / 4 / Charity

basil / 4 / Charity

I hope this makes sense and I've added the file I'm currently using for this exercise.

I all honesty I would prefer to use a query so the data is pulled automatically but I'm unsure if this is possible at all.

Thank you in advance

Google Sheets - Wastage reduction

Solution

  • OK well the short answer is just a couple of typos in the formula - it should always specify a height of 1 row and a width of 3 columns and offset an extra 3 columns for each row you go down:

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

    enter image description here

    A single formula version would use almost the same calculation:

    =ArrayFormula(vlookup(ceiling((row(A2:A)-1)/5,1)+1,{row(A2:A),responses!A2:Z},
{mod(row(A2:A)-2,5)*3+12,mod(row(A2:A)-2,5)*3+13,mod(row(A2:A)-2,5)*3+14}))