Check column for values outside pre-defined list

yrpsoa_

New Member
Joined
Feb 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 :)




1644851114102.png


1644851945249.png



EDIT: I'd also like to avoid VBA if possible!
 
Last edited by a moderator:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
For anyone interested, I did find a half solution, using the following video, and substituting for "" - however, slick as it may be, it's not very computationally efficient, and has stopped excel responding on a couple of occassions. This may be because my source data is 30000+ rows and the allowed values list is ~200. I'm sure this would work fine on smaller data sets, so putting here in case it's useful to someone.

youtube.com/watch?v=L7s6Dni1dG8
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top