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
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
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?
