table help
 

HI all, I have a spreadsheet dealing with pallets that come into the
company I work for. On each pallet there may only be one model or many
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 model
number and 3rd is the quanity of that model on that pallet. Now I need
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 with
sale price etc.

Any help will be appreciated.

thanks


--
TAZGALSINH
------------------------------------------------------------------------
TAZGALSINH's Profile: http://www.excelforum.com/member.php...o&userid=31054
View this thread: http://www.excelforum.com/showthread...hreadid=507250

table help
 

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)
DataPivot 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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=507250

table help
 

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" wrote:


HI all, I have a spreadsheet dealing with pallets that come into the
company I work for. On each pallet there may only be one model or many
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 model
number and 3rd is the quanity of that model on that pallet. Now I need
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 with
sale price etc.

Any help will be appreciated.

thanks


--
TAZGALSINH
------------------------------------------------------------------------
TAZGALSINH's Profile: http://www.excelforum.com/member.php...o&userid=31054
View this thread: http://www.excelforum.com/showthread...hreadid=507250

table help
 

Ron Coderre Wrote:
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)
DataPivot 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
------------------------------------------------------------------------
TAZGALSINH's Profile: http://www.excelforum.com/member.php...o&userid=31054
View this thread: http://www.excelforum.com/showthread...hreadid=507250

table help
 

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


--
TAZGALSINH
------------------------------------------------------------------------
TAZGALSINH's Profile: http://www.excelforum.com/member.php...o&userid=31054
View this thread: http://www.excelforum.com/showthread...hreadid=507250