I'm working on a project to create a template that uses one of my regular exports and changes the way the data is structured. I've attached a sample spreadsheet below.
https://docs.google.com/spreadsheets/d/1mFo_VpdeXpWkpLqMNnCBAT_93rx_JCjG508VsEwMoL4/edit?usp=sharing
The "Source Data" tab is my export, it's a list of orders where rows are repeated for each unique product that the customer has purchased. The Order Numbers are repeated for as many rows that the order has.
I'm trying to add additional row(s) to each order on my "Corrected Data" tab based on conditions and perform a calculation that would change the data that the additional row outputs.
The conditions / rules are as follows.
To add any additional rows the "Shipping Cost" of the order (column O) must be > 0
If an order has all products with a Tax Class of > 0 then 1 additional row should be added. The Order Number needs to be the same, the "Product Code" (column J) should have "TAXABLE-SHIPPING", the Product Name (column K) should be "Taxable Shipping" and the "Product Price" (column T) needs the following calculation: ROUNDDOWN((ShippingCost/(100+TaxClass)*100),2). Every other column can remain blank.
If an order has all products with a Tax Class of 0 then 1 additional row should be added. The Order Number needs to be the same, the "Product Code" (column J) should have "ZERORATED-SHIPPING", the Product Name (column L) should be "Zero Rated Shipping" and the "Product Price (column T) should be the same as the "Shipping Cost".
If the order has a mixture of Tax Classes then 2 additional rows should be added. The Order Number needs to be the same for both rows, one row should have "Zero Rated"and "ZERORATED-SHIPPING" in the Product Name/Code Columns and the other row should have "Taxable Shipping" and "TAXABLE-SHIPPING". respectively.
The "Product Price" on these two rows needs to do the following calculations and apply to their respective rows.
- ZERORATED: ROUNDDOWN(((SUM(Product Prices that have 0 Tax Class)/Order Subtotal)*Shipping Cost),2)
- TAXABLE: ROUNDUP(((((SUM(Product Prices that are > 0 Tax Class)/Order Subtotal)*Shipping Cost)/(100+Tax Class)*100),2)
So far, I've managed to get this working with a bit of help to the point where 1 additional row is being added when the Shipping Cost is > 0. This is only with a few select columns rather than the whole dataset. This can be seen in the "Partial Solution" tab.
I don't even know where to begin with the 2nd additional row with multiple tax classes, or how to wrap the calculations into that.
I appreciate that this one is quite lengthy and difficult, perhaps formula isn't the best solution here but I was hoping a single cell array formula would be able to do this. Any help at all pointing me in the right direction would be hugely appreciated! <3