Creating a Useful Spreadsheet From Miva Merchant Order Export Data: Step Two

Now that you have your Miva Merchant order export data imported into Excel, a few quick tricks with some conditional formulas will clean up the duplicate tax, shipping and order total entries.

Step Two: Create your Excel formulas

Note that in the examples that follow, the columns containing shipping and billing details have been compressed for a more concise display. The actual cell references are accurate for a standard orders.dat import.

1. As you can see in the example below, the tax, shipping and order total are repeated for each item in an order. This repetition causes the totals shown to be inflated.
   
2. We'll start by eliminating the duplicate sales tax entries for those orders with more than one product. The first step is to insert a new column for our revised sales taxes. Click the column heading immediately to the right of the ORDER_TAX column (this will be the ORDER_SHIP column) to select the entire column. Click the Insert menu, then select Columns.
   
3. Name the new column TAX. Click in cell AJ2 and paste the following formula into the cell
   =IF(A2=A1,0,AI2)
   
4. Drag and fill the formula into the remaining cells in the column. You will see in column AJ that the sales tax is now listed only once for each individual product. The TAX column total is now accurate.
5. Insert a new column beside ORDER_SHIP by clicking the column heading immediately to the right (this will be the ORDER_TOTL column), selecting the Insert menu, and clicking on Columns.
6. Name this new column SHIPPING. Click in cell AL2 and paste the following formula into the cell:
   =IF(A2=A1,0,AK2)
   
7. Drag and fill the formula into the remaining cells in the column. You will see in column AL that the shipping is now shown only once per order, and the shipping total is now correct.
   
8. Lastly, click in cell AN1 and name the column TOTAL. Click in cell AN2 and paste the following formula into the cell:
   =IF(A2=A1,0,AM2)
9. Drag and fill the formula into the remaining cells in the column. You will see in column AN that the order total is now shown only once per order. The total for all orders is now correct.

To consolidate product and attribute pricing, proceed to Step Three.

Client Center  |  Articles & Resources
© 2005-2006 Tiny Queen Designs. All site content property of Tiny Queen Designs.