Formula For Summarizing Data

T

Tiziano

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.
 
M

Max

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 ..
 
M

Max

Slight typo ..
In L2:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2:$D$55500>=120)))
Click to expand...

In L12 should be:
=IF($G2="","",SUMPRODUCT(($A$2:$A$55500=$G2)*($D$2:$D$55500>120)))
 
E

Epinn

Max,

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

Just curious.

Epinn

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

Max

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,

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

Just curious.

Epinn
 
M

Max

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.
 
T

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.
 
E

Epinn

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



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.
 
E

Epinn

I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

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



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.
 
E

Epinn

If we want to remove trailing spaces, this is one way to do it.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Max, feel free to correct me if I am wrong.

Epinn

I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

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



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.
 
E

Epinn

This is better. =SUBSTITUTE(A1," ","")

Epinn

If we want to remove trailing spaces, this is one way to do it.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Max, feel free to correct me if I am wrong.

Epinn

I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

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



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.
 
T

Tiziano

Why not use the function =trim(a1)?
--
Tiziano

This is better. =SUBSTITUTE(A1," ","")

Epinn

If we want to remove trailing spaces, this is one way to do it.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Max, feel free to correct me if I am wrong.

Epinn

I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

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



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.
 
E

Epinn

Believe it or not ......

Previously, I asked exactly the same question when someone else didn't use TRIM( ).

I have totally forgotten about it. I think these days I do things the hard way. <g> By the way, I was just thinking aloud and I learn a lot this way. Thanks for reminding me.

Max, do you see any difference between TRIM( ) and SUBSTITUTE( )?

How is your project going? We all appreciate feedback. I am particularly interested in the speed.

Epinn

Why not use the function =trim(a1)?
--
Tiziano

This is better. =SUBSTITUTE(A1," ","")

Epinn

If we want to remove trailing spaces, this is one way to do it.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Max, feel free to correct me if I am wrong.

Epinn

I have one concern.

Part no. ABC123
and
Part no. ABC123___

may be counted twice because of the trailing spaces in the latter??

Max, in that case, we have to do LEN ( ), SUBSTITUTE ( ) etc., right?

Epinn

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



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.
 
M

Max

I'd just use TRIM, Epinn. Btw, thanks for answering Tiziano's new query.
With the large ranges involved here (55,000 rows), of course, Excel needs
time to re-calc. It won't be immediate. What's important is correct results
are returned.

Believe it or not ......

Previously, I asked exactly the same question when someone else didn't use
TRIM( ).

I have totally forgotten about it. I think these days I do things the hard
way. <g> By the way, I was just thinking aloud and I learn a lot this way.
Thanks for reminding me.

Max, do you see any difference between TRIM( ) and SUBSTITUTE( )?

How is your project going? We all appreciate feedback. I am particularly
interested in the speed.

Epinn
 
E

Epinn

Max,

Happy Monday to you.

I have never doubted your formulae. In case you don't know, I am a fan of SUMPRODUCT and I would definitely use SUMPRODUCT for this project.

I don't have much experience with large worksheet(s) and that's the only reason why I am interested in how 55,000 rows perform with SUMPRODUCT.

Epinn

I'd just use TRIM, Epinn. Btw, thanks for answering Tiziano's new query.
With the large ranges involved here (55,000 rows), of course, Excel needs
time to re-calc. It won't be immediate. What's important is correct results
are returned.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Believe it or not ......

Previously, I asked exactly the same question when someone else didn't use
TRIM( ).

I have totally forgotten about it. I think these days I do things the hard
way. <g> By the way, I was just thinking aloud and I learn a lot this way.
Thanks for reminding me.

Max, do you see any difference between TRIM( ) and SUBSTITUTE( )?

How is your project going? We all appreciate feedback. I am particularly
interested in the speed.

Epinn
 
M

Max

how 55,000 rows perform with SUMPRODUCT.
It'll work, but we really need to give Excel time to crunch it all <g>

Max,

Happy Monday to you.

I have never doubted your formulae. In case you don't know, I am a fan of
SUMPRODUCT and I would definitely use SUMPRODUCT for this project.

I don't have much experience with large worksheet(s) and that's the only
reason why I am interested in how 55,000 rows perform with SUMPRODUCT.

Epinn
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Formula For Data Summary 7
How can I copy data produced by a formula, not the formula? 2
Help With Searching For Strings In Part Numbers 3
Function help 7
Aging Formula Help 4
VLOOKUP and IF together 3
Calculate total by row number 4
Finding top n most frequent - 2 subtley different problems 1

Top