How to use FILTER function in excel to to exclude certain values?
First, this is becoming very difficult for me to explain, so please bear with me. I want to make it easier to calculate for a puzzle (killer sudokus basically). So I made an excel table for all possible permutations possible with 1 to 9, their sum, and their number of digits. Now I am trying to use the Filter function (I don't want to mess with the table now) to give me all results that use a particular no. of digits, have a particular sum, and exclude certain digits.
The process till now: I made all the permutations with all the different digits laid out in different columns. Then I made a no. of digits column (ranging from 2 to 8). Then I made a sum column. Then I made a concatenate column to make it easier to look at (along with commas). Now my table is ready.
Using the filter function, I was able to get values based on no. of digits and sum, but I am not able to work out how to exclude certain digits.
The code I have used
`FILTER(Table14,(Table14[no of digits]=Sheet4!B1)*(Table14[sum]=Sheet4!B4),"not found")`As you can see, this is not working because I have to choose only the correct combinations by inputting in sheet 4.
So instead of '*' I used + in the 1 to 9 columns, not the 'sum' and 'no of digits' columns but that gave me all the values.
As an e.g. no. of digits is 4 sum is 20 what are all the possible combinations?
1, 2, 8, 9
1, 3, 7, 9
1, 4, 6, 9
1, 4, 7, 8
1, 5, 6, 8
2, 3, 6, 9
2, 3, 7, 8
2, 4, 5, 9
2, 4, 6, 8
2, 5, 6, 7
3, 4, 5, 8
3, 4, 6, 7
but I now how do I get it to exclude all combinations with the value 6, leaving me with:
1, 2, 8, 9
1, 3, 7, 9
1, 4, 7, 8
2, 3, 7, 8
2, 4, 5, 9
3, 4, 5, 8
and how do I do it for multiple values, say I don't want either a 6 or an 8.
1, 3, 7, 9
2, 4, 5, 9
The main table contains the following columns
In a different sheet I input the sum, the number of digits and the digits to exclude.
I am having trouble with the last part, (digits to exclude). How do I do that? I know it can be done with the filtering in the table itself by checking on blanks for the digits I don't want, but I want to get it in a different sheet and table altogether.
Thanks.
1 Answer
It's not the prettiest of formulas, but I would consider adding a helper column to your table that you could hide. This column would contain a formula to highlight which combination contain a 6 or an 8. See the image as a possible exaple.
I hope this gives you some ideas. Brad
1
