castower.blogg.se

Product 5 (Amount: 9.00 USD, Number of cases: 8) Product 3 (Amount: 19.00 USD, Number of cases: 8)

Product 2 (Amount: 16.00 USD, Number of cases: 8) Product 1 (Amount: 32.00 USD, Number of cases: 10)

Product 10 (Amount: 13.00 USD, Number of cases: 17) Product 9 (Amount: 17.00 USD, Number of cases: 33) Product 7 (Amount: 35.00 USD, Number of cases: 35) Product 5 (Amount: 9.00 USD, Number of cases: 25) Product 3 (Amount: 19.00 USD, Number of cases: 17) Product 2 (Amount: 16.00 USD, Number of cases: 28) Product 1 (Amount: 32.00 USD, Number of cases: 30) One solution that has proved promising is using the function from this solution combined with a regex/substitute hybrid, this can be seen on the 'expand_split' tab in the linked sheetand following table: What I would like is an output where I could perhaps create a set of columns for each product and have the number of cases for that product on each row for our customer.Ī sample with a small variety of the order lines is here

I have tried several things with regex, splitting lines by CHAR(10), but can't quite find a simple solution to it. There are 26 products in total, and while they do seem to come in the same order, not all products are present in each order. Product 10 (Amount: 12.00 USD, Number of cases: 4) Product 9 (Amount: 13.00 USD, Number of cases: 1) Product 6 (Amount: 17.00 USD, Number of cases: 3) Product 5 (Amount: 35.00 USD, Number of cases: 3) Product 2 (Amount: 16.00 USD, Number of cases: 2) Unfortunately Jotform's product list places all the items into one multi-line cell, so when trying to total the number of cases for each item to know how much to order from our suppliers it is difficult to extract the information.Īs an example the data we receive would be something like: Product 1 (Amount: 32.00 USD, Number of cases: 66) I am using Jotform for customers to place quarterly orders, and we then receive this data into a google sheet.