| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to consolidate/sum a list
I have a simple spreadsheet in the following format ...
Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. -- Larry Wallis. |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to consolidate/sum a list
Select the part numbers header included, do datafilteradvanced filter,
select unique records only and copy to another location, assume that the original part numbers are in A1:A1000, assume that you copied the unique list to H1:H200, now in I2 put =SUMIF($A$2:$A$1000,H2,$C$2:$C$1000) copy down all adjacent unique records -- Regards, Peo Sjoblom "Larry Wallis" wrote in message ... I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. -- Larry Wallis. |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to consolidate/sum a list
"Peo Sjoblom" wrote in message
... Select the part numbers header included, do datafilteradvanced filter, select unique records only and copy to another location, assume that the original part numbers are in A1:A1000, assume that you copied the unique list to H1:H200, now in I2 put =SUMIF($A$2:$A$1000,H2,$C$2:$C$1000) copy down all adjacent unique records -- Regards, Peo Sjoblom "Larry Wallis" wrote in message ... I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. -- Larry Wallis. Excellent. Thanks Peo. -- Larry Wallis. |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to consolidate/sum a list
Larry,
Another way is to use the subtotals feature Go to the Data menu and click Subtotals, follow the directions. Regards, Jim Cone San Francisco, USA "Larry Wallis" wrote in message ... I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. Larry Wallis. |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to consolidate/sum a list
Hi Larry
just a note on Jim's comments - it's important to SORT by part number before using data / subtotals ... an alternative approach is using Pivot Tables - Debra Dalgleish has some good instructions on her website at www.contextures.com/tiptech.html Cheers JulieD "Jim Cone" wrote in message ... Larry, Another way is to use the subtotals feature Go to the Data menu and click Subtotals, follow the directions. Regards, Jim Cone San Francisco, USA "Larry Wallis" wrote in message ... I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. Larry Wallis. |
|
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to consolidate/sum a list
"JulieD" wrote in message
... Hi Larry just a note on Jim's comments - it's important to SORT by part number before using data / subtotals ... an alternative approach is using Pivot Tables - Debra Dalgleish has some good instructions on her website at www.contextures.com/tiptech.html Cheers JulieD "Jim Cone" wrote in message ... Larry, Another way is to use the subtotals feature Go to the Data menu and click Subtotals, follow the directions. Regards, Jim Cone San Francisco, USA "Larry Wallis" wrote in message ... I have a simple spreadsheet in the following format ... Cell A1 with a heading Part Number, B1 heading Description and C1 heading Quantity. Under the headings is a list of 100 rows and a part number can appear more than once. What I would like to do is take all the unique part numbers and put them into a separate list and then sum the totals for these part numbers. Can anyone give me and idea how to do this please? Many thanks. Larry Wallis. And thanx to you too Jim and Julie. -- Larry Wallis. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidate 3 lists with duplicates into one list | Excel Worksheet Functions | |||
| I need to consolidate a list into rows | Excel Discussion (Misc queries) | |||
| Matching items in 2 list to consolidate to one list | Excel Discussion (Misc queries) | |||
| How to consolidate/sum a list | Excel Discussion (Misc queries) | |||
| How to consolidate/sum a list | Excel Worksheet Functions | |||
Hybrid Mode
