Ok so, as the title suggests, I have a column full of values, and I want to check whether they are "allowed" values.
I'd like the calculation contained within one cell, so no helper columns etc.
I've achieved something similar with =SUMPRODUCT(--(ISNUMBER(MATCH([values],[allowed_values],0)))). This works fine, and is largely what I'm trying to achieve, to give you an idea.
However, this new scenario is slightly more complicated than that, so the above doesn't quite work. In this scenario, there are multiple values within some cells (see screenshot 1), so EUR and USD might be "allowed" values, but MYR and HKD are not. I want to count the occurrences of these outliers.
One route I tried was substituting the allowed values for "", counting how many characters were left in each cell, and dividing by 3 (all values are 3 char long). This works ok, but excel only allows substitutions one at a time, and I have a list of ~20 allowed values, and there's no function for multiple substitutions (as IFS is to IF). This leads to a very cumbersome formula, which is difficult to maintain if the allowed values list changes, and doesn't work if the char length is variable. I'm sure there must be a more elegant solution, which the bright sparks on this forum are itching to impress with!
NB. This is a live query within power query, so I can't manipulate the data within the table - any manipulation has to be 'passive' from within a formula. Also, the example shown is currency, but I'm looking for something that could work on other columns as well (see screenshot 2).
Any help on this would be much appreciated!
Many thanks in advance
EDIT: I'd also like to avoid VBA if possible!
I'd like the calculation contained within one cell, so no helper columns etc.
I've achieved something similar with =SUMPRODUCT(--(ISNUMBER(MATCH([values],[allowed_values],0)))). This works fine, and is largely what I'm trying to achieve, to give you an idea.
However, this new scenario is slightly more complicated than that, so the above doesn't quite work. In this scenario, there are multiple values within some cells (see screenshot 1), so EUR and USD might be "allowed" values, but MYR and HKD are not. I want to count the occurrences of these outliers.
One route I tried was substituting the allowed values for "", counting how many characters were left in each cell, and dividing by 3 (all values are 3 char long). This works ok, but excel only allows substitutions one at a time, and I have a list of ~20 allowed values, and there's no function for multiple substitutions (as IFS is to IF). This leads to a very cumbersome formula, which is difficult to maintain if the allowed values list changes, and doesn't work if the char length is variable. I'm sure there must be a more elegant solution, which the bright sparks on this forum are itching to impress with!
NB. This is a live query within power query, so I can't manipulate the data within the table - any manipulation has to be 'passive' from within a formula. Also, the example shown is currency, but I'm looking for something that could work on other columns as well (see screenshot 2).
Any help on this would be much appreciated!
Many thanks in advance
EDIT: I'd also like to avoid VBA if possible!
Last edited by a moderator:
