If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Countdiff with more criteria
Hello,
... Morefunc is one of the better add-ins available so don't let Bernd try to convince you otherwise. Definitely. ... Point is: Morefunc can be as fine as anybody wishes for - without a license or without open source code you run the risk that it contains a virus or that you lose its functionality in future. That's why most companies' policies are not allowing to use it (or any of this kind). JP obviously intends to use his solution in his company. So let us not suggest to him to use Morefunc without checking his company's IT policy. Whether or not you agree to the number 1 of my Excel Don'ts http://sulprobil.com/html/excel_don_ts.html is a stronger but different point: What I cannot use in my company I won't use at all. Regards, Bernd |
#12
|
|||
|
|||
Countdiff with more criteria
Hi Bernd,
You have a point with your remark on the company policy. Although not explicitly forbidden, our IT department will not support it. Therefore, I used the function that T. Valko suggested. With kind regards, JP "Bernd P" wrote in message ... Hello, ... Morefunc is one of the better add-ins available so don't let Bernd try to convince you otherwise. Definitely. ... Point is: Morefunc can be as fine as anybody wishes for - without a license or without open source code you run the risk that it contains a virus or that you lose its functionality in future. That's why most companies' policies are not allowing to use it (or any of this kind). JP obviously intends to use his solution in his company. So let us not suggest to him to use Morefunc without checking his company's IT policy. Whether or not you agree to the number 1 of my Excel Don'ts http://sulprobil.com/html/excel_don_ts.html is a stronger but different point: What I cannot use in my company I won't use at all. Regards, Bernd |
#13
|
|||
|
|||
Countdiff with more criteria
Hello JP,
... You have a point with your remark on the company policy. Although not explicitly forbidden, our IT department will not support it. Therefore, I used the function that T. Valko suggested. ... Ok, since you ticked the first point off, have a look now at the second one of my Excel Dont's, please: http://sulprobil.com/html/w-rule.html Avoid array formulas you (or the majority of your colleagues) do not understand or which you will fail to maintain later on. My thesis is that there are brilliant people around here suggesting nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel users do not fully understand them, and the (array) formulas are not tackling the right problem space anyway. A huge bulk of the questions here can and maybe should be reasonably answered with Pivot tables (I suggest to search for Herbert Seidenberg's Pivot table solutions here). And another nice part of the challenges can be solved with VBA macros which encapsulate the functionality so that the casual user can easily use them (I suggest to have a look at my website). Before you "give in" to an array formula I suggest to think about helper columns and normal worksheet functions. Maybe you are satisfied with Biff's suggestion for now. But I suggest to put my suggestion into a cell comment next to his formula. I bet somebody will struggle with the array formula later on. Kind regards, Bernd |
#14
|
|||
|
|||
Countdiff with more criteria
Hi Harlan,
Is also a nice alternative. Sorry for late reply. With kind regards, JP "Harlan Grove" wrote in message ... "T. Valko" wrote... ... Morefunc is one of the better add-ins available so don't let Bernd try to convince you otherwise. Definitely. Cust (customer) refers to Sheet1!$A$2:$A$11 Eng (engineer) refers to Sheet1!$B$2:$B$11 Dates refers to Sheet1!$C$2:$C$11 I'm assuming the dates are all within the same year so we're only going to test the dates for the month. E2:E3 are the 1st of the month dates 1/11/2009, 1/12/2009 F1:G1 = JP, Bill If there would be multiple result formulas, it may be better to define a 4th name BIN referring to the formula =ROW(Cust)-MIN(ROW(Cust)) (omission of +1 intentional). The you could use formulas like =COUNT(1/FREQUENCY(MATCH(Cust,Cust,0)*(Eng=F$1)*(MONTH(Date s)=MONTH ($E2)),Bin))-1 which doesn't have to be entered as an array formula [because it doesn't call IF, and FREQUENCY always returns an array, so its results are handled like array constants]. |
#15
|
|||
|
|||
Countdiff with more criteria
Hi Bernd,
I fully agree not use functions you don't understand and will have a closer look to your website. With kind regards, JP "Bernd P" wrote in message ... Hello JP, ... You have a point with your remark on the company policy. Although not explicitly forbidden, our IT department will not support it. Therefore, I used the function that T. Valko suggested. ... Ok, since you ticked the first point off, have a look now at the second one of my Excel Dont's, please: http://sulprobil.com/html/w-rule.html Avoid array formulas you (or the majority of your colleagues) do not understand or which you will fail to maintain later on. My thesis is that there are brilliant people around here suggesting nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel users do not fully understand them, and the (array) formulas are not tackling the right problem space anyway. A huge bulk of the questions here can and maybe should be reasonably answered with Pivot tables (I suggest to search for Herbert Seidenberg's Pivot table solutions here). And another nice part of the challenges can be solved with VBA macros which encapsulate the functionality so that the casual user can easily use them (I suggest to have a look at my website). Before you "give in" to an array formula I suggest to think about helper columns and normal worksheet functions. Maybe you are satisfied with Biff's suggestion for now. But I suggest to put my suggestion into a cell comment next to his formula. I bet somebody will struggle with the array formula later on. Kind regards, Bernd |
#16
|
|||
|
|||
Countdiff with more criteria
you run the risk that it contains a virus
or that you lose its functionality in future. And you run that exact same risk using one of your UDFs. -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, ... Morefunc is one of the better add-ins available so don't let Bernd try to convince you otherwise. Definitely. ... Point is: Morefunc can be as fine as anybody wishes for - without a license or without open source code you run the risk that it contains a virus or that you lose its functionality in future. That's why most companies' policies are not allowing to use it (or any of this kind). JP obviously intends to use his solution in his company. So let us not suggest to him to use Morefunc without checking his company's IT policy. Whether or not you agree to the number 1 of my Excel Don'ts http://sulprobil.com/html/excel_don_ts.html is a stronger but different point: What I cannot use in my company I won't use at all. Regards, Bernd |
#17
|
|||
|
|||
Countdiff with more criteria
So, if you don't understand a "complex" array formula what are the chances
that you're going to understand VBA code? I suggest to search for Herbert Seidenberg's Pivot table solutions His solution to *everything* is a pt and he only posts solutions for Excel 2007. What about the people that don't use Excel 2007? I guess they're SOL. -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello JP, ... You have a point with your remark on the company policy. Although not explicitly forbidden, our IT department will not support it. Therefore, I used the function that T. Valko suggested. ... Ok, since you ticked the first point off, have a look now at the second one of my Excel Dont's, please: http://sulprobil.com/html/w-rule.html Avoid array formulas you (or the majority of your colleagues) do not understand or which you will fail to maintain later on. My thesis is that there are brilliant people around here suggesting nifty (array) formulas. But 95% (hmm, maybe even 99%) of all Excel users do not fully understand them, and the (array) formulas are not tackling the right problem space anyway. A huge bulk of the questions here can and maybe should be reasonably answered with Pivot tables (I suggest to search for Herbert Seidenberg's Pivot table solutions here). And another nice part of the challenges can be solved with VBA macros which encapsulate the functionality so that the casual user can easily use them (I suggest to have a look at my website). Before you "give in" to an array formula I suggest to think about helper columns and normal worksheet functions. Maybe you are satisfied with Biff's suggestion for now. But I suggest to put my suggestion into a cell comment next to his formula. I bet somebody will struggle with the array formula later on. Kind regards, Bernd |
#18
|
|||
|
|||
Countdiff with more criteria
Hello Biff,
... you run the risk that it contains a virus or that you lose its functionality in future. And you run that exact same risk using one of your UDFs. No. My UDFs are open source, fairly short, and a competent third person could easily adapt them to any future Excel version, I honestly believe. ... So, if you don't understand a "complex" array formula what are the chances that you're going to understand VBA code? You do not need to understand to program or to maintain them, you need to know how to use them - that's encapsulation. ... His solution to *everything* is a pt and he only posts solutions for Excel 2007. What about the people that don't use Excel 2007? I guess they're SOL. ... Not to *everything*. But have a look at http://sulprobil.com/html/pfreq.html (look at the "statistic on engineers" example, please). You can see that yours and Harlan's approach is about 6x faster than my Pstat approach for 100 data records. But for 1,000 data records my VBA approach is already about 5x faster than yours and Harlan's (who's falling over if there is any empty record, btw). And the approach which I deem to be state-of-the-art is another 3x faster: Prepare the data with Pfreq and then apply a Pivot table. You can see all variants in my sample file(s). Biff, I like your and Harlan's clever solutions and I think I learned a lot from both of you. But for many Excel challenges a Pivot table seems to be a preferrable solution. Btw: I think its not fair to say that I only post Excel 2007 solutions. I publish my worksheet formulas and my VBA macros which can be used in earlier versions as well. Regards, Bernd |
#19
|
|||
|
|||
Countdiff with more criteria
Btw: I think its not fair to say that I only post Excel 2007 solutions.
Who said that? My response was directed at this statement: I suggest to search for Herbert Seidenberg's Pivot table solutions *Every* post made by Herbert Seidenberg is a 2007 pt. I disagree with just about everything you said. You're not going to change my mind and I'm not going to change your mind so.... -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello Biff, ... you run the risk that it contains a virus or that you lose its functionality in future. And you run that exact same risk using one of your UDFs. No. My UDFs are open source, fairly short, and a competent third person could easily adapt them to any future Excel version, I honestly believe. ... So, if you don't understand a "complex" array formula what are the chances that you're going to understand VBA code? You do not need to understand to program or to maintain them, you need to know how to use them - that's encapsulation. ... His solution to *everything* is a pt and he only posts solutions for Excel 2007. What about the people that don't use Excel 2007? I guess they're SOL. ... Not to *everything*. But have a look at http://sulprobil.com/html/pfreq.html (look at the "statistic on engineers" example, please). You can see that yours and Harlan's approach is about 6x faster than my Pstat approach for 100 data records. But for 1,000 data records my VBA approach is already about 5x faster than yours and Harlan's (who's falling over if there is any empty record, btw). And the approach which I deem to be state-of-the-art is another 3x faster: Prepare the data with Pfreq and then apply a Pivot table. You can see all variants in my sample file(s). Biff, I like your and Harlan's clever solutions and I think I learned a lot from both of you. But for many Excel challenges a Pivot table seems to be a preferrable solution. Btw: I think its not fair to say that I only post Excel 2007 solutions. I publish my worksheet formulas and my VBA macros which can be used in earlier versions as well. Regards, Bernd |
#20
|
|||
|
|||
Countdiff with more criteria
Hello Biff,
Fair enough. It's easy for me to watch out for COUNT(1/FREQUENCY()), SUM(... (FREQUENCY()) or SUMPROUCT(()/COUNTIF()) suggestions and to point out what Charles already stated some time ago. I cite him at: http://sulprobil.com/html/count_unique.html There are no useless posts. They can still serve as a bad example. Regards, Bernd |
Thread Tools | |
Display Modes | |
|
|