table help

[TAZGALSINH]

HI all, I have a spreadsheet dealing with pallets that come into th
company I work for. On each pallet there may only be one model or man
different models with varying quantitys.

16 R874SLM 6
R874WM 5
R872SLM 1
R754SLM 3
R765SLM 1

17 R953SLM 8
R95STM 1
R774WM 1
R754SLM 1
R884SLM 1
R872SLM 1
R343GM 1
R96STM 2

18 R957SLM 12
R95STM 1
R383SLM 1
R345SLM 1
R771GM 1

19 R84STM 13
R85STM 4
R754SLM 2
R96STM 1

20 R345SLM 2
R95STM 3
R891WM 2
R895SLM 4
R754SLM 1
R95STM 1
R951WM 1
R96STM 2

21 R354WM 11
R774WM 1
R957WM 1
R84STM 1
R96STM 1
R757SL 1

the first collum is the pallet number (1 onwards), 2nd is the mode
number and 3rd is the quanity of that model on that pallet. Now I nee
to total these up into another sheet.

for example
model total
r774m 28
r295sl 57

This has to be in a table form as I do alot of calulations to do wit
sale price etc.

Any help will be appreciated.

thank

 

[Ron Coderre]

From what you described, I'd recommend a Pivot Table

Start by putting column headings above the data: Pallet, Model, Qty

Select the data (including headings)
Data>Pivot Table>
Source: Excel...click [Next]
Source range: (already selected)...click [Next]
Choose a source for the pivot table
Click the [Layout] button

ROW: Drag the Model field here
Dbl-clidk the Model field and set Subtotals to None

DATA: Drag the Qty field here
(If Count of Qty displays....Dbl-click it and set it for SUM)

click the [OK] button
Click the [Finish] button

Does that help?

Regards,
Ro

 

[Guest]

Take a look at Pivot Tables as this is a classic example of when to use one.

The wizard can be found under Data --- Pivot Table and Pivot Chart Report.

HTH.

 

[TAZGALSINH]

From what you described, I'd recommend a Pivot Table

Start by putting column headings above the data: Pallet, Model, Qty

Select the data (including headings)
Data>Pivot Table>
Source: Excel...click [Next]
Source range: (already selected)...click [Next]
Choose a source for the pivot table
Click the [Layout] button

ROW: Drag the Model field here
Dbl-clidk the Model field and set Subtotals to None

DATA: Drag the Qty field here
(If Count of Qty displays....Dbl-click it and set it for SUM)

click the [OK] button
Click the [Finish] button

Does that help?

Regards,
Ron

thanks for the reply, that doesn't seem to work. its making the table
but seem to be summing up how many times the model appears on the list.
eg r246stm is on 5 pallets so it shows 5.

 

[TAZGALSINH]

done it, I had to change the field settings to summerize by sum, thank