Can Excel Do this or do I need to use MS Access?
Posted by T Janecek on February 11, 2001 7:09 PM
I am hoping that I can get help with this situation. I am exporting information from an AS400 database into Excel. The report I am trying to generate is a subtotal of all orders received from a date range. The way the information is extracted, it gives line items on all orders. The problem this creates is that I have 40,000 lines from 4000 orders. Is there a way to take the line items on the same orders and combine them to see just the total for that particular order? If I am not making myself clear, I will try to show below what the output actually looks like. I would really appreciate the help! I hate to print out 200 pages when I can do it in 50.
Here's the way it looks:
ORDER NO. QUANTITY PRICE EXTENDED
1234 5 1.00 5.00
1234 10 2.00 20.00
1234 20 0.10 2.00
1234 1 10.00 10.00
2345 20 1.00 20.00
2345 5 1.00 5.00
The report goes on and on similar to this. Can I compare the Order No. column and then run a total of the Extended Price column or is this something that I should do in Access or another database application? The other thing I would like to do is to eliminate the other lines as well to see just the total order amount.
THANK YOU FOR ANY HELP!!!!!!