| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create asummarized list from larger list
I have a spreadsheet that will constantly updated by customers. I want to
create a formula to automatically summarize a list of various components. For example: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Material 1 Thickness 1 I want a formula on the spreadsheet to summarize this data so that it will produce a list as follows: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Many thanks in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create asummarized list from larger list
One formulas play which delivers the automated summary that you seek
Source data is assumed entered in A2:B2 down In D2: =IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*( B$2:B2=B2))1,"",ROW())) Leave D1 blank In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(A:A,SMALL($D: $D,ROWS($1:1)))) Copy E2 to F2. Select D2:F2, copy down to cover the max expected extent of source data, say down to F100? Minimize col D. Cols E and F will return the required summary, all neatly packed at the top & dynamic to the source data as it changes/adds on. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "tomhelle" wrote: I have a spreadsheet that will constantly updated by customers. I want to create a formula to automatically summarize a list of various components. For example: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Material 1 Thickness 1 I want a formula on the spreadsheet to summarize this data so that it will produce a list as follows: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create asummarized list from larger list
Sweet! Thanks Max - really appreciate your help!!
"Max" wrote: One formulas play which delivers the automated summary that you seek Source data is assumed entered in A2:B2 down In D2: =IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*( B$2:B2=B2))1,"",ROW())) Leave D1 blank In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(A:A,SMALL($D: $D,ROWS($1:1)))) Copy E2 to F2. Select D2:F2, copy down to cover the max expected extent of source data, say down to F100? Minimize col D. Cols E and F will return the required summary, all neatly packed at the top & dynamic to the source data as it changes/adds on. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "tomhelle" wrote: I have a spreadsheet that will constantly updated by customers. I want to create a formula to automatically summarize a list of various components. For example: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Material 1 Thickness 1 I want a formula on the spreadsheet to summarize this data so that it will produce a list as follows: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create asummarized list from larger list
My pleasure. Great to hear that.
-- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "tomhelle" wrote in message ... Sweet! Thanks Max - really appreciate your help!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create asummarized list from larger list
Hi Max,
Your first solution works perfect. Now, if I could ask for one more favor...lets say I have a Column C with a material quantity. I want a formula to add up the total quantities for the summarized list of materials. Example of source data: Column A Column B Column C Material 1 Thickness 1 Qty 1 Material 1 Thickness 2 Qty 2 Material 2 Thickness 1 Qty 3 Material 1 Thickness 1 Qty 4 I want a formula to calculate the total Qty of the summaried list of materials so that I would have a resulting list as follows: Material 1 Thickness 1 Qty 1+ Qty4 Material 1 Thickness 2 Qty 2 Material 2 Thickness 1 Qty 3 Max wrote: One formulas play which delivers the automated summary that you seek Source data is assumed entered in A2:B2 down In D2: =IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*( B$2:B2=B2))1,"",ROW())) Leave D1 blank In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(A:A,SMALL($D: $D,ROWS($1:1)))) Copy E2 to F2. Select D2:F2, copy down to cover the max expected extent of source data, say down to F100? Minimize col D. Cols E and F will return the required summary, all neatly packed at the top & dynamic to the source data as it changes/adds on. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "tomhelle" wrote: I have a spreadsheet that will constantly updated by customers. I want to create a formula to automatically summarize a list of various components. For example: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Material 1 Thickness 1 I want a formula on the spreadsheet to summarize this data so that it will produce a list as follows: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create asummarized list from larger list
Never mind my second request. I figured it out using SUMPRODUCT. Thanks again
Max. "tomhelle" wrote: Hi Max, Your first solution works perfect. Now, if I could ask for one more favor...lets say I have a Column C with a material quantity. I want a formula to add up the total quantities for the summarized list of materials. Example of source data: Column A Column B Column C Material 1 Thickness 1 Qty 1 Material 1 Thickness 2 Qty 2 Material 2 Thickness 1 Qty 3 Material 1 Thickness 1 Qty 4 I want a formula to calculate the total Qty of the summaried list of materials so that I would have a resulting list as follows: Material 1 Thickness 1 Qty 1+ Qty4 Material 1 Thickness 2 Qty 2 Material 2 Thickness 1 Qty 3 Max wrote: One formulas play which delivers the automated summary that you seek Source data is assumed entered in A2:B2 down In D2: =IF(COUNTA(A2:B2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*( B$2:B2=B2))1,"",ROW())) Leave D1 blank In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(A:A,SMALL($D: $D,ROWS($1:1)))) Copy E2 to F2. Select D2:F2, copy down to cover the max expected extent of source data, say down to F100? Minimize col D. Cols E and F will return the required summary, all neatly packed at the top & dynamic to the source data as it changes/adds on. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "tomhelle" wrote: I have a spreadsheet that will constantly updated by customers. I want to create a formula to automatically summarize a list of various components. For example: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 Material 1 Thickness 1 I want a formula on the spreadsheet to summarize this data so that it will produce a list as follows: Column A Column B Material 1 Thickness 1 Material 1 Thickness 2 Material 2 Thickness 1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup to create a list from a larger list | Excel Discussion (Misc queries) | |||
| Create summarized list from larger list | Excel Discussion (Misc queries) | |||
| Automatically Deleting a List from Another (Larger) List | Excel Discussion (Misc queries) | |||
| Producing an automated list from a larger list | Excel Worksheet Functions | |||
| Drop-down list Populated by a Subset of a larger list | Excel Worksheet Functions | |||
Hybrid Mode
