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?

Improve this question
5
  • We have several similar questions : Transpose Ranges repeated by row
    – Rubén - Volunteer Moderator - ♦
    Commented Dec 12, 2019 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
    Commented Dec 12, 2019 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
    Commented Dec 13, 2019 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
    Commented Dec 13, 2019 at 3:20
  • Good. Please post your solutions as answer.
    – Rubén - Volunteer Moderator - ♦
    Commented Dec 13, 2019 at 3:21

1 Answer 1

Reset to default
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
2
  • 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
    Commented Dec 13, 2019 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
    Commented Dec 13, 2019 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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