Hello, what I am trying to do is figure out if there is a easy way to automate calculations in a excel spread sheet. Basically I download a sheet from a website, I can’t control what the download contains or the order or anything like that, I just have to take it like it is. So what I need to do is take the numbers that are in each row, for each item, let’s say each item has 4 rows, 1 for sale amount of the item and 3 for different fee’s, what I need to do is calculate the price after the fee’s have been subtracted. I use Excel 2007 and 2003, either one will work if that matters.
So instead of 4 rows that say 100, 10, 5 and 5 I need one row that says 80. I can filter and delete the extra rows if need be but I just need to narrow it down to the amount after fee’s have been taken out. Once I get to that point I have a SQL query that will import it into a Access database and update the inventory records.
Below is a example and I also attached a spread sheet, 4 items with 4 rows each, ideally it would be great if there was a way to automate calculating each item and then creating or editing an existing row with the amount after fees are taken out.
Please let me know if there is a way to make this work. I have never written a macro or gotten really in depth with the functionality of Excel so any help is appreciated.
Date Inventory Number Amount Details
9/14/2011 E1 100 Sales amount of item
9/14/2011 E1 10 Fee 1
9/14/2011 E1 5 Fee 2
9/14/2011 E1 5 Fee 3
9/15/2011 E2 200 Sales amount of item
9/15/2011 E2 20 Fee 1
9/15/2011 E2 5 Fee 2
9/15/2011 E2 5 Fee 3
9/16/2011 E3 300 Sales amount of item
9/16/2011 E3 30 Fee 1
9/16/2011 E3 5 Fee 2
9/16/2011 E3 5 Fee 3
9/17/2011 E4 400 Sales amount of item
9/17/2011 E4 40 Fee 1
9/17/2011 E4 5 Fee 2
9/17/2011 E4 5 Fee 3