I have a worksheet with about 55,000 line items in it regarding sales
history for the past nine months.
* Column A has part numbers. (The same part number can appear multiple
times)
* Column B has the date the order for the part number was received.
* Column C has the date the ordered part number shipped.
* Column D has the number of days elapsed between cols. B and C.

What I would like to do is summarize all this data such that:
* One column lists all the part numbers. (No duplicates!)
* For each part number, I'd like to know how many times the order shipped
within 1-30 days, 31-60 days, 61-90 days, 91-120 days, more than 120 days.

Can anybody help with an Excel formula?

Thanks.
--
Tiziano

One set up which could deliver it here ..

Assume source data/formulas in cols A to D starts in row2 down

Since it's going to be calc intensive, set the calc mode to Manual.
Click Tools Options Calculation tab Check "Manual" OK

Enter the col labels in G1:L1 :
Part#, 1-30, 31-60, 61-90, 91-120, 120

Put in F2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW()))
Copy F2 down to cover the max expected extent of data, say down to F55500.
Leave F1 empty. Hide away col F.

Put in G2:
=IF(ROW(A1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,ROW( A1))))
Copy G2 down by the smallest extent sufficient to cover the max expected
number of unique Part#s, say down to G1000. Col G will dynamically extract
the list of unique Part#s, all neatly bunched at the top

Then place

In H2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=1)*($D$2:$D$55500<=30)))

In I2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=31)*($D$2:$D$55500<=60)))

In J2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=61)*($D$2:$D$55500<=90)))

In K2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=91)*($D$2:$D$55500<=120)))

In L2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=120)))

Copy H2:L2 down to L1000 (consistent with col G's fill)

If desired, switch off zeros display in the sheet for a neater look:
Click Tools Options View tab Uncheck "Zero values" OK

Cols G to L will provide the required summary. Press F9 to recalc, but only
whenever necessary (eg: after completing new data entries for the day). Adapt
to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote:
I have a worksheet with about 55,000 line items in it regarding sales
history for the past nine months.
* Column A has part numbers. (The same part number can appear multiple
times)
* Column B has the date the order for the part number was received.
* Column C has the date the ordered part number shipped.
* Column D has the number of days elapsed between cols. B and C.

What I would like to do is summarize all this data such that:
* One column lists all the part numbers. (No duplicates!)
* For each part number, I'd like to know how many times the order shipped
within 1-30 days, 31-60 days, 61-90 days, 91-120 days, more than 120 days.

Can anybody help with an Excel formula?

Thanks.
--
Tiziano

Slight typo ..

In L2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=120)))

In L12 should be:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500120)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"In L12" should read: "In L2", of course
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

Since there are 55,000 lines, I am wondering if it will be a lot faster if we use Advanced FilterUnique Records to pull out the unique part numbers.

Just curious.

Epinn

"Max" wrote in message ...
"In L12" should read: "In L2", of course
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Epinn,

As always <g, I'm presuming it's to be structured dynamic to the source
data (changes thereof) wherever feasible. Anyway the performance hit here is
already inevitable due to the large SP ranges required to handle the source
data extents. That's where the manual calc mode comes in handy. In daily
operation, when we're all set to go (after new source data inputs, data
changes, etc), we just press F9 and retire elsewhere for a well deserved
10-15 min break.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Epinn" wrote in message
...
Max,

Since there are 55,000 lines, I am wondering if it will be a lot faster if
we use Advanced FilterUnique Records to pull out the unique part numbers.

Just curious.

Epinn

Thanks, I will try it out and see what happens.
--
Tiziano

"Max" wrote in message
...
One set up which could deliver it here ..

Assume source data/formulas in cols A to D starts in row2 down

Since it's going to be calc intensive, set the calc mode to Manual.
Click Tools Options Calculation tab Check "Manual" OK

Enter the col labels in G1:L1 :
Part#, 1-30, 31-60, 61-90, 91-120, 120

Put in F2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW()))
Copy F2 down to cover the max expected extent of data, say down to F55500.
Leave F1 empty. Hide away col F.

Put in G2:
=IF(ROW(A1)COUNT(F:F),"",INDEX(A:A,SMALL(F:F,ROW( A1))))
Copy G2 down by the smallest extent sufficient to cover the max expected
number of unique Part#s, say down to G1000. Col G will dynamically extract
the list of unique Part#s, all neatly bunched at the top

Then place

In H2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=1)*($D$2:$D$55500<=30)))

In I2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=31)*($D$2:$D$55500<=60)))

In J2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=61)*($D$2:$D$55500<=90)))

In K2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=91)*($D$2:$D$55500<=120)))

In L2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2 :$D$55500=120)))

Copy H2:L2 down to L1000 (consistent with col G's fill)

If desired, switch off zeros display in the sheet for a neater look:
Click Tools Options View tab Uncheck "Zero values" OK

Cols G to L will provide the required summary. Press F9 to recalc, but
only
whenever necessary (eg: after completing new data entries for the day).
Adapt
to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote:
I have a worksheet with about 55,000 line items in it regarding sales
history for the past nine months.
* Column A has part numbers. (The same part number can appear multiple
times)
* Column B has the date the order for the part number was received.
* Column C has the date the ordered part number shipped.
* Column D has the number of days elapsed between cols. B and C.

What I would like to do is summarize all this data such that:
* One column lists all the part numbers. (No duplicates!)
* For each part number, I'd like to know how many times the order shipped
within 1-30 days, 31-60 days, 61-90 days, 91-120 days, more than 120
days.

Can anybody help with an Excel formula?

Thanks.
--
Tiziano

Tiziano, you're welcome. Let me know how it went for you. As mentioned in my
response to Epinn, due to the inherent calc-intensiveness here, it's
advisable to set the book's calc mode to Manual first. Then do the set-up.
And then press F9 to recalc whenever required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Thanks, I will try it out and see what happens.
--
Tiziano

Max,
I would like to add some sort of formula (separate from the formulas you
gave me before) that lets me know how many unique part numbers I have in
column A of my spreadsheet.
Can you help?
Thanks.
--
Tiziano

"Max" wrote in message
...
Tiziano, you're welcome. Let me know how it went for you. As mentioned in
my response to Epinn, due to the inherent calc-intensiveness here, it's
advisable to set the book's calc mode to Manual first. Then do the set-up.
And then press F9 to recalc whenever required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Thanks, I will try it out and see what happens.
--
Tiziano

Max,



Can the poster use the following? I learned the COUNTIF formula from the experts.



If the part numbers are strictly numbers, then FREQUENCY ( ) can be used.



=SUMPRODUCT(--(FREQUENCY(A1:A55000,A1:A55000)0))



If the part numbers are text, numbers or a combination of both, then use COUNTIF ( ).



=SUMPRODUCT((A1:A55000<"")/COUNTIF(A1:A55000,A1:A55000&""))



Blanks will not be counted in both cases.



Epinn



"Tiziano" wrote in message ...
Max,
I would like to add some sort of formula (separate from the formulas you
gave me before) that lets me know how many unique part numbers I have in
column A of my spreadsheet.
Can you help?
Thanks.
--
Tiziano

"Max" wrote in message
...
Tiziano, you're welcome. Let me know how it went for you. As mentioned in
my response to Epinn, due to the inherent calc-intensiveness here, it's
advisable to set the book's calc mode to Manual first. Then do the set-up.
And then press F9 to recalc whenever required.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Thanks, I will try it out and see what happens.
--
Tiziano

Believe Epinn has provided the answers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tiziano" wrote in message
...
Max,
I would like to add some sort of formula (separate from the formulas you
gave me before) that lets me know how many unique part numbers I have in
column A of my spreadsheet.
Can you help?
Thanks.
--
Tiziano