| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula For Summarizing Data
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 --- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
"In L12" should read: "In L2", of course
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 "Epinn" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
This is better. =SUBSTITUTE(A1," ","")
Epinn "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
Why not use the function =trim(a1)?
-- Tiziano "Epinn" wrote in message ... This is better. =SUBSTITUTE(A1," ","") Epinn "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 "Tiziano" wrote in message ... Why not use the function =trim(a1)? -- Tiziano "Epinn" wrote in message ... This is better. =SUBSTITUTE(A1," ","") Epinn "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 "Epinn" wrote in message ... 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
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 --- "Epinn" wrote in message ... 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 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
Max,
Happy Monday to you. What's important is correct results are returned. << 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 "Max" wrote in message ... 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 --- "Epinn" wrote in message ... 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 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula For Summarizing Data
how 55,000 rows perform with SUMPRODUCT.
It'll work, but we really need to give Excel time to crunch it all <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Epinn" wrote in message ... Max, Happy Monday to you. What's important is correct results are returned. << 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
| Macro question | Excel Worksheet Functions | |||
| Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Linear Mode
