Need help working calculations in Excel Spread sheet!!
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.
Thanks,
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
Example-Sheet1.xls
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.
Thanks,
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
Example-Sheet1.xls
als315
If you will import data into Access why not to do this work there? It will be very simple query. Sales amount - Sum(Fee)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
After following those instruction the sheet will look like the attached - it's as easy to do for 1000 rows as for 20...
barry
27308246.xls
barry
27308246.xls
Barry,
For variable rows, how about SUMPRODUCT with Inventory ID, Sales* & Fee* as the variables.
Thanks
Rob H
For variable rows, how about SUMPRODUCT with Inventory ID, Sales* & Fee* as the variables.
Thanks
Rob H
ASKER
I'm curious if that would work too?
ASKER
Thanks for the help everyone. I'm starting this project soon.