Need Better Item Inventory

New Contributor

For each customer order, info in the Items column (E) is just text and the Order Total column (F) is a formula that gets recreated for each new order number. This doesn't give me any reporting capability on the types and quantities of items sold. For each new order I would like to have a drop-down box with a list of media types, and I choose one; and next to it a new column to drop in the quantity of that media type I just chose; and next to that a new column with a formula for the total price of that line item. I can handle those items.

 

My issue is: how do I take a database of single-row orders with no reporting, and turn it into a database of orders that contain multiple rows of specific item names and quantities and item totals, per order. And there's no rhyme or reason from one order to the next for how many line items it might have. For example: order #266 contains four separate items and quantities, and the order total next to it combines those items and quantities into a price. I want to break those down into individual items, one per line, with its own quantity and item total. So how would I automate adding lines for individual items within each order, but not add new lines to all the other columns like date and order number and order total?Spreadsheet 1.jpg

2 Replies
Hi Mike,
Interesting (annoying?) challenge! Are your groupd items in Col E consistently separated with + signs?
Hi Colin.
They are, but only because that seemed like an appropriate character to use. I'm not married to it.