Replies

Your second formula worked for me and I thank you very much. The data I sent was just a subset of the original data and I neglected to include the zero values, but I added that condition to the formula you sent and it worked. Once I saw your logic I was able to figure the problem out. I feel like I had tried every possible array configuration but that one. It just didn’t occur to me to set all the conditions within the IF.

Thank you again for your insight.

Regards,

Hi Hans,

My apologies. The error was mine entirely. I had neglected to insert the tilde in the string. Once I inserted it, voila!, the problem was resolved. D-u-u-h…

However, there is another twist on this. Now what they want to do is create a folder on a shared drive and drop a copy of the email into that folder, which creates a .msg file there, which, I believe, is nothing more than a link pointing to the original email. Now the question becomes, is there a way to add a hyperlink in Excel to the particular file in that folder. I already know I can link to the folder itself, but I’ve been unable to get any farther than that. It seems what they want to do is to have shared users be able to see the original email.

As always, I would very much appreciate any insights you have on this.

Let me thank you for your efforts, which never fail to impress.

Regards,

Hi Hans,

Let me begin by thanking you for your response.

I tried what you suggested, but I get an error back that says “The messaging interface has returned an unkown error. If the problem persists, restart Outlook.”

What I did was create a new message with “Test Message” as the subject and then I sent it to one of my colleagues. Then I followed your instructions. I tried using the subject with quotes and without, but I got the same error either way.

I should also say that the messages will be in the Sent Items folder, not the Inbox. I did specify Sent Items as the folder.

Any other ideas?

Hans, you prove yourself a genius once again. The formula works perfectly. I just couldn’t get the construction. I was trying to put the third condition outside the FREQUENCY and it didn’t work. It didn’t occur to me to construct the formula with the extra condition(s) inside the FREQUENCY.

Thank you again. This is terrific.

Regards,

Hans, you are a life saver (as always). The formula works perfectly.

Now, please don’t shoot me, but I have a second, related question. Is it possible to amend this formula to add another condition? For example, this formula returns a count of the unique names in column B based on Title in column D. Suppose I need to further break down the count by seeing how many unique names that are, for example, Managing Directors in North America or Europe or Japan (column H)?

I know a pivot table will do all this, but for the purposes of this model a formula is much more efficient. We are currently using pivot tables to get this info, but they need to be rebuilt every week and the various filters need to be applied manually, all of which is time consuming. A formula that does it all would be so much better.

Thanks very much for the formula you provided. That alone will be a big help. It would be great if there were a formula to add more conditions.

Regards,

Thanks, Steve. I was close, but I didn’t think to multiply the hits for the percentile in the array formula by ISNUMBER. I tried a lot of variations and I knew I was close, but I just couldn’t get the right answer.

Thanks again. You bring a lot of relief to a lot of Excel lives…

Regards,

Hi Steve,

Well, I went through those posts and looked at the code and I still can’t make it work. I suspect now that there is something in our work environment that I don’t know that is the stopper. I have opened a support call with Adobe (we have a contract with them) hoping that they can point me in the right direction.

Let me thank you for your efforts. You are one knowledgeable and helpful guy.

George

Hi Hans and thanks for your response.

The bad news is that it is not possible to use Access. I only sent a small portion of the actual downloaded data and the problem I described is actually only a part of a larger model. I need to code the entire thing so a user can click a button and get a result.

The good news is that I had a breakthrough and (I think) am on the way to fully resolving the problem in VBA. Basically I created two arrays and several counters and I was able to generate accurate numbers for the first three companies. I checked the results manually in a pivot table, so I’m pretty sure I have it now, but I’ll know for certain on Monday when I pick it up again.

Thanks again for your reply and your suggestion. I’m sure that if I don’t have the answer I’ll be back for more advice.

Regards,

Hi Hans and thanks for your response.

The bad news is that it is not possible to use Access. I only sent a small portion of the actual downloaded data and the problem I described is actually only a part of a larger model. I need to code the entire thing so a user can click a button and get a result.

The good news is that I had a breakthrough and (I think) am on the way to fully resolving the problem in VBA. Basically I created two arrays and several counters and I was able to generate accurate numbers for the first three companies. I checked the results manually in a pivot table, so I’m pretty sure I have it now, but I’ll know for certain on Monday when I pick it up again.

Thanks again for your reply and your suggestion. I’m sure that if I don’t have the answer I’ll be back for more advice.

Regards,

Actually I had thought of that in the first place, but the user said that this approach wouldn’t be practical for him, for whatever reason. I couldn’t find any functions that would work in such a situation and I got to thinking that maybe I’m just missing something, that perhaps there is a function that would work in such a situation. It looks like Legare’s approach is the only one that would work in this particular situation.

Actually I had thought of that in the first place, but the user said that this approach wouldn’t be practical for him, for whatever reason. I couldn’t find any functions that would work in such a situation and I got to thinking that maybe I’m just missing something, that perhaps there is a function that would work in such a situation. It looks like Legare’s approach is the only one that would work in this particular situation.

Very clever. Yet another simple and elegant approach. Thank you for the insight.

Unfortunately the table in the example file was just for illustration. The real table(s) are more complex.

Thanks again…

Very clever. Yet another simple and elegant approach. Thank you for the insight.

Unfortunately the table in the example file was just for illustration. The real table(s) are more complex.

Thanks again…

Yes, it does work, but I’m not sure why. I tried your formula with one minus sign in front of each array and that worked as well. Could you explain how your formula works?

Thanks very much for your response. I’ve found the SUMPRODUCT function to be very useful, did not see its use here until you pointed it out.

Yes, it does work, but I’m not sure why. I tried your formula with one minus sign in front of each array and that worked as well. Could you explain how your formula works?

Thanks very much for your response. I’ve found the SUMPRODUCT function to be very useful, did not see its use here until you pointed it out.