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?