Check If Value Exists in PivotTable (as a PivotItem)

[EnigmaMatter]

Hello,

I have a PivotTable full of Customer data. When I select a Customer in the Report Filter/PageField, it shows me a list of products purchased.

How do I check the entire range of products purchased by that customer for a specific product? If the range does not contain that product name, it would show me a MsgBox saying "No.".

[Norie]

Why don't you use the raw data rather than the pivot table?

[EnigmaMatter]

There is too much raw data to sort through, and it is a very messy export from an accounting program.

[Norie]

You could use a formula.

[EnigmaMatter]

Norie,

I appreciate that, but I really don't think that methodology will work with the rest of the application.

I can't post the application (and an example workbook would be wonky). Is there not a way to check a range for a value and then return a message if the value exists in the range (anywhere)?

[Norie]

Yes, you could find out if a value exists in a range by using a formula like MATCH which could be implemented in code via Application.Match.

[EnigmaMatter]

Norie,

I will give that a shot.

[EnigmaMatter]

Norie,

The MATCH formula returns a value that tells me the position of the lookup_value in the lookup_array. However, if the lookuo_value doesn't exist, it simply tells me how many values are in the array.ell

I had an idea to use a COUNTA formula to tell me how many values are in the array, and if the MATCH and COUNTA values were the same, then I'd know there is no match. However, this won't work if the lookup_value is the last value in the array -- I wouldn't know if there wasn't a match or not. Is there a boolean version of the MATCH formula that returns TRUE/FALSE?

[EnigmaMatter]

I used the Match_Type (didn't see that...).

I will now try to make something that works and will let you know what I come up with.

[Norie]

Match doesn't return the no of values in the array if the value you are looking for doesn't exist, it returns an error.

[EnigmaMatter]

Norie,

I will try to implement the following into my application:

Please Login or Register  to view this content.

If it works, I will mark thread as solved.

[EnigmaMatter]

You're right, but only if you do not omit the match-type.

[Norie]

If you omit the match type it defaults to 1 and Match finds the largest value less than or equal to the value being looked for.

[EnigmaMatter]

Thank you; I appreciate it.