title: Adding additional rows to data based on multiple criteria
body: <p>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.</p>
  <p><a href="https://docs.google.com/spreadsheets/d/1mFo_VpdeXpWkpLqMNnCBAT_93rx_JCjG508VsEwMoL4/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1mFo_VpdeXpWkpLqMNnCBAT_93rx_JCjG508VsEwMoL4/edit?usp=sharing</a></p>
  <p>The &quot;Source Data&quot; 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.</p>
  <p>I'm trying to add additional row(s) to each order on my &quot;Corrected Data&quot; tab based on conditions and perform a calculation that would change the data that the additional row outputs.</p>
  <p>The conditions / rules are as follows.</p>
  <ol>
  <li><p>To add any additional rows the &quot;Shipping Cost&quot; of the order (column O) must be &gt; 0</p>
  </li>
  <li><p>If an order has all products with a Tax Class of &gt; 0 then 1 additional row should be added. The Order Number needs to be the same, the &quot;Product Code&quot; (column J) should have &quot;TAXABLE-SHIPPING&quot;, the Product Name (column K) should be &quot;Taxable Shipping&quot; and the &quot;Product Price&quot; (column T) needs the following calculation: ROUNDDOWN((ShippingCost/(100+TaxClass)*100),2). Every other column can remain blank.</p>
  </li>
  <li><p>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 &quot;Product Code&quot; (column J) should have &quot;ZERORATED-SHIPPING&quot;, the Product Name (column L) should be &quot;Zero Rated Shipping&quot; and the &quot;Product Price (column T) should be the same as the &quot;Shipping Cost&quot;.</p>
  </li>
  <li><p>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 &quot;Zero Rated&quot;and &quot;ZERORATED-SHIPPING&quot; in the Product Name/Code Columns and the other row should have &quot;Taxable Shipping&quot; and &quot;TAXABLE-SHIPPING&quot;. respectively.</p>
  <p>The &quot;Product Price&quot; on these two rows needs to do the following calculations and apply to their respective rows.</p>
  <ol>
  <li>ZERORATED: ROUNDDOWN(((SUM(Product Prices that have 0 Tax Class)/Order Subtotal)*Shipping Cost),2)</li>
  <li>TAXABLE: ROUNDUP(((((SUM(Product Prices that are &gt; 0 Tax Class)/Order Subtotal)*Shipping Cost)/(100+Tax Class)*100),2)</li>
  </ol>
  </li>
  </ol>
  <p>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 &gt; 0. This is only with a few select columns rather than the whole dataset. This can be seen in the &quot;Partial Solution&quot; tab.</p>
  <p>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.</p>
  <p>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! &lt;3</p>