Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
scan rows
hi,
i need to scan rows in these 3 columns where i found the first cell (C3)in column c is the same in the the second cell (C4) until it doesnt'changes then sum B3 with b4, b5 ecc. This is a kind a subtotal. 1-column a column b column c 2-Date Weight name 3-12/06/2003 182,00 RIMA SPA 4-12/06/2003 26,00 RIMA SPA 5-12/06/2003 182,00 RIMA SPA 6-12/06/2003 120,00 RIMA SPA 7-13/06/2003 62,20 RIMA SPA 8-13/06/2003 3,75 RIMA SPA 9-16/06/2003 0,60 RIMA SPA 10-16/06/2003 525,00 PERINI SPA 11-16/06/2003 242,50 RIMA SPA 12-17/06/2003 11,98 RIMA SPA 13-19/06/2003 0,30 RIMA SPA 14-19/06/2003 252,00 RIMA SPA 15-19/06/2003 128,60 RIMA SPA 16-19/06/2003 4,10 RIMA SPA The effect will be so 12/06/2003 510 RIMA SPA 13/06/2003 65,95 RIMA SPA 16/06/2003 0,60 RIMA SPA 16/06/2003 525,00 PERINI SPA 16/06/2003 242,50 RIMA SPA 17/06/2003 11,98 RIMA SPA 19/06/2003 385 RIMA SPA Thanks in advance.John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
scan rows
A pivot table almost works here. If you can live with a difference.
If I selected the range and did Data|Pivottable, then follow the wizard until I saw the step with the Layout button and clicked that, I could drag the Date to the row field. Then drag Name to the row field. Then drag Weight to the Data field (if it says something besides Sum, double click on it and choose sum.) Then follow the wizard until completion. I got this: Sum of Weight Date name Total 06/12/2003 RIMA SPA 510 06/12/2003 Total 510 06/13/2003 RIMA SPA 65.95 06/13/2003 Total 65.95 06/16/2003 PERINI SPA 525 RIMA SPA 243.1 06/16/2003 Total 768.1 06/17/2003 RIMA SPA 11.98 06/17/2003 Total 11.98 06/19/2003 RIMA SPA 385 06/19/2003 Total 385 Grand Total 1741.03 And if I double clicked on the Date Field (almost looks like a button), I could suppress totals for that field. Then it looked like: Sum of Weight Date name Total 06/12/2003 RIMA SPA 510 06/13/2003 RIMA SPA 65.95 06/16/2003 PERINI SPA 525 RIMA SPA 243.1 06/17/2003 RIMA SPA 11.98 06/19/2003 RIMA SPA 385 Grand Total 1741.03 But the Rima Spa was combined for 6/16/2003. Is that important enough to toss the pivot table? If yes, you may want to look at Data|subtotal. Run it twice. Once for Name and once for date (be sure to uncheck the "Replace current subtotals" when you run it the second time.) Then you could use the outlining symbols to the left to show/hide details. Jonh wrote: hi, i need to scan rows in these 3 columns where i found the first cell (C3)in column c is the same in the the second cell (C4) until it doesnt'changes then sum B3 with b4, b5 ecc. This is a kind a subtotal. 1-column a column b column c 2-Date Weight name 3-12/06/2003 182,00 RIMA SPA 4-12/06/2003 26,00 RIMA SPA 5-12/06/2003 182,00 RIMA SPA 6-12/06/2003 120,00 RIMA SPA 7-13/06/2003 62,20 RIMA SPA 8-13/06/2003 3,75 RIMA SPA 9-16/06/2003 0,60 RIMA SPA 10-16/06/2003 525,00 PERINI SPA 11-16/06/2003 242,50 RIMA SPA 12-17/06/2003 11,98 RIMA SPA 13-19/06/2003 0,30 RIMA SPA 14-19/06/2003 252,00 RIMA SPA 15-19/06/2003 128,60 RIMA SPA 16-19/06/2003 4,10 RIMA SPA The effect will be so 12/06/2003 510 RIMA SPA 13/06/2003 65,95 RIMA SPA 16/06/2003 0,60 RIMA SPA 16/06/2003 525,00 PERINI SPA 16/06/2003 242,50 RIMA SPA 17/06/2003 11,98 RIMA SPA 19/06/2003 385 RIMA SPA Thanks in advance.John -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
scan rows
Why not use two levels of subtotal, subtotal on column C, then on Column A.,
use the outline tool, and away you go Steve "Jonh" wrote in message ... hi, i need to scan rows in these 3 columns where i found the first cell (C3)in column c is the same in the the second cell (C4) until it doesnt'changes then sum B3 with b4, b5 ecc. This is a kind a subtotal. 1-column a column b column c 2-Date Weight name 3-12/06/2003 182,00 RIMA SPA 4-12/06/2003 26,00 RIMA SPA 5-12/06/2003 182,00 RIMA SPA 6-12/06/2003 120,00 RIMA SPA 7-13/06/2003 62,20 RIMA SPA 8-13/06/2003 3,75 RIMA SPA 9-16/06/2003 0,60 RIMA SPA 10-16/06/2003 525,00 PERINI SPA 11-16/06/2003 242,50 RIMA SPA 12-17/06/2003 11,98 RIMA SPA 13-19/06/2003 0,30 RIMA SPA 14-19/06/2003 252,00 RIMA SPA 15-19/06/2003 128,60 RIMA SPA 16-19/06/2003 4,10 RIMA SPA The effect will be so 12/06/2003 510 RIMA SPA 13/06/2003 65,95 RIMA SPA 16/06/2003 0,60 RIMA SPA 16/06/2003 525,00 PERINI SPA 16/06/2003 242,50 RIMA SPA 17/06/2003 11,98 RIMA SPA 19/06/2003 385 RIMA SPA Thanks in advance.John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i fax a scan | Excel Discussion (Misc queries) | |||
scan to excel | Excel Discussion (Misc queries) | |||
virus scan | Setting up and Configuration of Excel | |||
Scan Forms | Excel Discussion (Misc queries) | |||
HOW CAN I SCAN DATA WITH COLUMNS AND ROWS DIRECTLY INTO SPREADSHE. | New Users to Excel |