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!

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

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

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

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

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!!