0

I'm currently using Google Sheets and I'm capturing data with Google Forms.

What I'm receiving from Forms, inside the corresponding sheets is an entry with timestamp, clientID, item1, quantity1, item2, quantity2 (...itemN, quantityN). Where N at this time is 5, but I'm still solving how it happens for 1 and 2 before I add 3 through 5.

What I'm trying to do is merge the lists and quantity into a unified column(s)

Example

in:

timestampA, clientIDA, item1, quantity1, item2, quantity2... itemN, quantityN

timestampB, clientIDB, item1, quantity1, item2, quantity2... itemN, quantityN

out:

timestampA, clientIDA, item1, quantity1

timestampA, clientIDA, item2, quantity2

...

timestampA, clientIDA, itemN, quantityN

timestampB, clientIDB, item1, quantity1

timestampB, clientIDB, item2, quantity2

...

timestampB, clientIDB, itemN, quantityN

item1 and quantity1 are required, item2 through itemN and quantity2 through quantityN are not.

It's also important that I don't need to manually transpose as the data entering is continuous and I won't be monitoring it.

I tried to do it with pivots, but as item1 and item2 have different column names it won't aggregate. If I added a row on top and added new titles then execute the query below, it only calls 1 column (item1 or item2) not (item1 and item2) as they're not unique.

Does anyone have any idea how I can do this?

  • We have several similar questions : Transpose Ranges repeated by row – Rubén Dec 12 '19 at 21:41
  • Thanks Ruben, apologies for the similar questions. Just didn't know how to approach this question and finding a resolution. I tried going down the path of using Viktor's unpivot, albeit unsuccessful at this time. I'll keep trying. – Felix Pau Dec 12 '19 at 23:13
  • I got some help and ended up with this, that calls the data in the sorted manner I needed for analysis. The formula I'm using is =query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null") I tried to append ORDER BY but can't seem to get it right in terms of converting col1 to date. I'm generally guessing its something on the lines of '"&TEXT(DATEVALUE("COL1"),"yyyy-mm-dd")&"'" but I'm unsure if I need to convert the column first with arrayformula, would that be around the current formula? Any help is appreciated – Felix Pau Dec 13 '19 at 2:31
  • I got it, for the noobies like me who are searching =query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null order by Col1") apparently i didnt need to convert col1 – Felix Pau Dec 13 '19 at 3:20
  • Good. Please post your solutions as answer. – Rubén Dec 13 '19 at 3:21
1

The solution was that I didn't need to unpivot, just reorder.

=query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null order by Col1")

Also added order by Col1 to sort by date.

| improve this answer | |
  • In your original question your input is not making any sense, is every fourth comma that which you wanted to break it into a new row or you have a client with an arbitrary number of items? – CodeCamper Dec 13 '19 at 20:39
  • Client has an arbitrary item and quantity which requires a minimum of 1 but I'll eventually scale to 5. I wanted to break it out into multiple roles to do the calculation required to adjust stock, invoice etc. – Felix Pau Dec 13 '19 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.