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!!!!!!
Posted by Mark W. on February 11, 2001 7:40 PM
You can easily do this with a PivotTable.
1. Select a single cell from your data list.
2. Choose the Data PivotTable Report menu command.
3. Press the Next> button until you get to Step 3 of 4. Then drag the ORDER NO. to the COLUMN area and the EXTENDED to the DATA area. Press Next>.
4. At Step 4 of 4 press the Finish button to create the PivotTable on a new worksheet which you can print.
Posted by Dave Hawley on February 11, 2001 10:07 PM
Hi T Janecek
Excel should handle this without any problem using a Pivot Table. However if you have 4000 diffrent order # then you wont be able to set it up as Mark has suggested as Excel only has 256 columns. You will need to drag your Order # to the Row Field or Page field. But I may have misunderstood you as you may only need 50.
Even so if you set up your table in the Row field you can select "Show Pages" from the Pivot toolbar and Excel will automatically create 50 pages for you, ready to print.
Dave
OzGrid Business Applications
Posted by Mark W. on February 12, 2001 2:29 PM
Damn! :|
I do that all the time when I'm working from
memory. I mix up row and column orientation when
I'm selecting Grand Totals on the PivotTable Options
dialog, and when I'm specifying the orientation of
an Edit Fill Series... command. I always ponder,
"Does 'column' mean all in one column or across all
columns?" And, invariably I guess wrong and have to
repeat the step/command to get it right.
Posted by Dave Hawley on February 12, 2001 6:03 PM
That's just one of the great things with Pivot Tables, you can change them easily at any time. In fact with Excel 2000 new and improved Pivots you can leave the layout blank and do it directly from the sheet. If you don't have 2000 it's worth getting just for Pivot Tables and Pivot Charts.
Dave
OzGrid Business Applications
Posted by T Janecek on February 13, 2001 4:26 AM
I am starting to see that I really don't know how to utilize Excel to it's fullest potential. Do you know of any on-line sources for learning the ins and outs? I would also like to learn VBA implemtation as well.
Thanks for your input, I will try to work with it!!!!!!!!
TJ
