Excel or database program calculation?
 

I posted this question earlier, but perhaps I didn't describe it properly. I
have 3 columns of data. Width, Height & Quantity (example: 45 30 13). I also
have about 500 rows of records that consist of about 50 different size
combinations. I need a total number of pieces for each size. I can go through
the 500 rows and list all the sizes, then do a calculation to give me the
total. However, it takes a very long time to go through the 500 rows. Can
Excel somehow search and find all the size combinations and list them at the
beginning or end of the table with the total of each size? Or do I need to do
this in Access or Filemaker? Thanks to all who previously tried to help me
with this problem.

Excel or database program calculation?
 

You can obtain a list of unique records quite easily by means of advanced
filter - Debra Dalgleish shows how he

http://www.contextures.com/xladvfilter01.html

Then with this list (often on another sheet) it is quite easy to count how
many times each combination occurs on the other sheet. You could do this
with a SUMPRODUCT formula testing each of the three values, or you could
join the three values together in a helper column in the main sheet, and
then have a COUNTIF formula in the summary sheet.

Hope this helps (but it's getting a bit late here, so goodnight).

Pete

"Paul" wrote in message
...
I posted this question earlier, but perhaps I didn't describe it properly.
I
have 3 columns of data. Width, Height & Quantity (example: 45 30 13). I
also
have about 500 rows of records that consist of about 50 different size
combinations. I need a total number of pieces for each size. I can go
through
the 500 rows and list all the sizes, then do a calculation to give me the
total. However, it takes a very long time to go through the 500 rows. Can
Excel somehow search and find all the size combinations and list them at
the
beginning or end of the table with the total of each size? Or do I need to
do
this in Access or Filemaker? Thanks to all who previously tried to help me
with this problem.

Excel or database program calculation?
 

Paul

I believe that Pete and I have the answer. As I said in my post on your
original question, and as Pete says here, the Advanced Filter will find
unique values from a data list. I placed instructions in my other post:

http://www.microsoft.com/office/comm...0-5597b19cc72d

Certainly you should review the instructions by the consummate expert Debra
Dalgleish at the link provide by Pete. My instructions were for your specific
situation, but Debras will have more expert general info.

With my instructions, you can also count the number of occurrences of each
unique size in the total list, as well as the total quantity.

Hope this helps, let us know if you need clarification or further help.

SongBear



"Paul" wrote:

I posted this question earlier, but perhaps I didn't describe it properly. I
have 3 columns of data. Width, Height & Quantity (example: 45 30 13). I also
have about 500 rows of records that consist of about 50 different size
combinations. I need a total number of pieces for each size. I can go through
the 500 rows and list all the sizes, then do a calculation to give me the
total. However, it takes a very long time to go through the 500 rows. Can
Excel somehow search and find all the size combinations and list them at the
beginning or end of the table with the total of each size? Or do I need to do
this in Access or Filemaker? Thanks to all who previously tried to help me
with this problem.

Excel or database program calculation?
 

On Tue, 12 Feb 2008 18:11:00 -0800, Paul
wrote:

I posted this question earlier, but perhaps I didn't describe it properly. I
have 3 columns of data. Width, Height & Quantity (example: 45 30 13). I also
have about 500 rows of records that consist of about 50 different size
combinations. I need a total number of pieces for each size. I can go through
the 500 rows and list all the sizes, then do a calculation to give me the
total. However, it takes a very long time to go through the 500 rows. Can
Excel somehow search and find all the size combinations and list them at the
beginning or end of the table with the total of each size? Or do I need to do
this in Access or Filemaker? Thanks to all who previously tried to help me
with this problem.

This would be one output from a pivot table that I suggested earlier; using
about 150 rows of "made-up" data. But you can format the output in a variety
of ways.


Width Height Total
15 20 162
30 143
40 75
50 103
15 Total 483

25 20 129
30 94
40 116
50 116
25 Total 455

35 20 83
30 59
40 155
50 103
35 Total 400

45 20 127
30 87
40 72
50 122
45 Total 408

Grand Total 1746

--ron