Filter Formula to exclude everything except one value

H

hananak

Board Regular
Joined
Feb 10, 2022
Messages
110
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Dear All,

I am trying to do do a lookup based on two criteria, if the criteria matches, then it should not give all the results but the result I want. I tried various formulas on internet on Xlookup but not working and then I came across Filter function which is close to what I want but still not able to get the desired outcome.

Please see the attached picture, to see if that is possible.

Your expert advice will be appreciated.
 

Attachments

  • Picture1.png
    Picture1.png
    206.2 KB · Views: 13
Sort by date Sort by votes

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sorry, I realized that after. I think this should work.
Book1
ABCDEFGHIJ
1
2
3EmployeeAllowances
411068246Allowance 1Employee NoEmployeeAllowances
511068246Allowance 21106824611068246Allowance 4
611068246Allowance 311068246Allowance 3
711068246Allowance 4Allowance 1
810903301Allowance 1Allowance 2
910903301Allowance 2
1010903301Allowance 3
1110903301Allowance 4Employee No
1210903301Allowance 510903301
1310903301Allowance 6
1410903301Allowance 7Allowance 1
1510903301Allowance 8Allowance 2
16Allowance 3
17Allowance 4
18Allowance 5
19Allowance 6
20Allowance 7
21Allowance 8
Sheet2
Cell Formulas
RangeFormula
F7:F8,F14:F21F7=LET(u,UNIQUE(VSTACK($B$4:$C$15,$I$5:$J$6),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F5))
Dynamic array formulas.
 
Upvote 0
Sorry, I realized that after. I think this should work.
Cubist said:
Book1
ABCDEFGHIJ
1
2
3EmployeeAllowances
411068246Allowance 1Employee NoEmployeeAllowances
511068246Allowance 21106824611068246Allowance 4
611068246Allowance 311068246Allowance 3
711068246Allowance 4Allowance 1
810903301Allowance 1Allowance 2
910903301Allowance 2
1010903301Allowance 3
1110903301Allowance 4Employee No
1210903301Allowance 510903301
1310903301Allowance 6
1410903301Allowance 7Allowance 1
1510903301Allowance 8Allowance 2
16Allowance 3
17Allowance 4
18Allowance 5
19Allowance 6
20Allowance 7
21Allowance 8
Sheet2
Cell Formulas
RangeFormula
F7:F8,F14:F21F7=LET(u,UNIQUE(VSTACK($B$4:$C$15,$I$5:$J$6),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F5))
Dynamic array formulas.
Click to expand...
Thanks but not sure am getting this #CALC! error. Is this because am getting the data via Power Query Table (Column B and C in this particular example)?
 
Upvote 0
It shouldn't have any issue with table structure references. Does the Employee No. you're looking up exist in column B?
Book1
ABCDEFGHIJ
1
2
3EmployeeAllowances
411068246Allowance 1Employee NoEmployeeAllowances
511068246Allowance 21106824611068246Allowance 4
611068246Allowance 311068246Allowance 1
711068246Allowance 4Allowance 2
810903301Allowance 1Allowance 3
910903301Allowance 2
1010903301Allowance 3
1110903301Allowance 4Employee No
1210903301Allowance 51
1310903301Allowance 6
1410903301Allowance 7#CALC!<-Error because employee no 1 doesn't exist
1510903301Allowance 8
Sheet2
Cell Formulas
RangeFormula
F7:F8F7=LET(u,UNIQUE(VSTACK(tblPQ,tblExclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F5))
F14F14=LET(u,UNIQUE(VSTACK(tblPQ,tblExclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F12,))
Dynamic array formulas.
 
Upvote 0
Cubist said:
It shouldn't have any issue with table structure references. Does the Employee No. you're looking up exist in column B?
Book1
ABCDEFGHIJ
1
2
3EmployeeAllowances
411068246Allowance 1Employee NoEmployeeAllowances
511068246Allowance 21106824611068246Allowance 4
611068246Allowance 311068246Allowance 1
711068246Allowance 4Allowance 2
810903301Allowance 1Allowance 3
910903301Allowance 2
1010903301Allowance 3
1110903301Allowance 4Employee No
1210903301Allowance 51
1310903301Allowance 6
1410903301Allowance 7#CALC!<-Error because employee no 1 doesn't exist
1510903301Allowance 8
Sheet2
Cell Formulas
RangeFormula
F7:F8F7=LET(u,UNIQUE(VSTACK(tblPQ,tblExclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F5))
F14F14=LET(u,UNIQUE(VSTACK(tblPQ,tblExclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=F12,))
Dynamic array formulas.
Click to expand...
My source data (Column B and C) been pulled through PQ. That table has many columns but am just selecting the two columns (Employee no and Allowances Description) in the table in this way Data!$F$2:$G$405 (Part of Data Table). Yes, the Employee exist in the source data. Below is my formula in my original file.

=LET(u,UNIQUE(VSTACK(Data!$F$2:$G$405,Exclude[#All]),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)='Final Summary'!$D4))
 
Upvote 0
You can call the 2 columns in a table like this in blue. Replace "Table1" with your table name.
Secondly, don't grab the header of the Exclude table.
Rich (BB code): 
=LET(u,UNIQUE(VSTACK(Table1[[Employee]:[Allowances]],Exclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=M5))
Book1
FGHIJKLMNOPQ
1
2
3EmployeeAllowancesColumn3Column4
411068246Allowance 1SomethingSomethingEmployee NoEmployeeAllowances
511068246Allowance 2SomethingSomething1106824611068246Allowance 4
611068246Allowance 3SomethingSomething11068246Allowance 3
711068246Allowance 4SomethingSomethingAllowance 1
810903301Allowance 1SomethingSomethingAllowance 2
910903301Allowance 2SomethingSomething
1010903301Allowance 3SomethingSomething
1110903301Allowance 4SomethingSomethingEmployee No
1210903301Allowance 5SomethingSomething10903301
1310903301Allowance 6SomethingSomething
1410903301Allowance 7SomethingSomethingAllowance 1
1510903301Allowance 8SomethingSomethingAllowance 2
16Allowance 3
17Allowance 4
18Allowance 5
19Allowance 6
20Allowance 7
21Allowance 8
Sheet2
Cell Formulas
RangeFormula
M7:M8,M14:M21M7=LET(u,UNIQUE(VSTACK(Table1[[Employee]:[Allowances]],Exclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=M5))
Dynamic array formulas.
 
Upvote 0
Cubist said:
clude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1
Click to expand...

Cubist said:
You can call the 2 columns in a table like this in blue. Replace "Table1" with your table name.
Secondly, don't grab the header of the Exclude table.
Rich (BB code): 
=LET(u,UNIQUE(VSTACK(Table1[[Employee]:[Allowances]],Exclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=M5))
Book1
FGHIJKLMNOPQ
1
2
3EmployeeAllowancesColumn3Column4
411068246Allowance 1SomethingSomethingEmployee NoEmployeeAllowances
511068246Allowance 2SomethingSomething1106824611068246Allowance 4
611068246Allowance 3SomethingSomething11068246Allowance 3
711068246Allowance 4SomethingSomethingAllowance 1
810903301Allowance 1SomethingSomethingAllowance 2
910903301Allowance 2SomethingSomething
1010903301Allowance 3SomethingSomething
1110903301Allowance 4SomethingSomethingEmployee No
1210903301Allowance 5SomethingSomething10903301
1310903301Allowance 6SomethingSomething
1410903301Allowance 7SomethingSomethingAllowance 1
1510903301Allowance 8SomethingSomethingAllowance 2
16Allowance 3
17Allowance 4
18Allowance 5
19Allowance 6
20Allowance 7
21Allowance 8
Sheet2
Cell Formulas
RangeFormula
M7:M8,M14:M21M7=LET(u,UNIQUE(VSTACK(Table1[[Employee]:[Allowances]],Exclude),,1),FILTER(CHOOSECOLS(u,2),CHOOSECOLS(u,1)=M5))
Dynamic array formulas.
Click to expand...
It took me a while to apply this formula on my original file and now it is working flawlessly. The formula was not working because of the data type in PQ was different from the excel. It took me a while to understand that there is nothing wrong with the formula but actually the data type is the culprit. I have managed to fix it.

There is one last bit, if you could help me. Currently I am filtering the list based on the Employee no in F column, I would like to add one more criteria and that is Cost code in the same F column. Could you please built that in the formula? Please see the picture.

Thanks.
 

Attachments

  • Picture3.png
    Picture3.png
    236.4 KB · Views: 4
Upvote 0
Try:
Book1
ABCDEFGHIJ
1
2
3Cost CodeEmployeeAllowancesColumn 4Cost Code
4124511068246Allowance 1Something1297EmployeeAllowances
5124511068246Allowance 2Something11068246Allowance 4
6124511068246Allowance 3SomethingEmployee No11068246Allowance 3
7124511068246Allowance 4Something11068246
8129710903301Allowance 1Something
9129710903301Allowance 2Somethingnot found
10129710903301Allowance 3Something
11129710903301Allowance 4Something
12129710903301Allowance 5Something
13129710903301Allowance 6SomethingEmployee No
14129710903301Allowance 7Something10903301
15129710903301Allowance 8Something
16Allowance 1
17Allowance 2
18Allowance 3
19Allowance 4
20Allowance 5
21Allowance 6
22Allowance 7
23Allowance 8
Sheet2
Cell Formulas
RangeFormula
F9,F16:F23F9=LET(u,UNIQUE(VSTACK(Table1[[Cost Code]:[Allowances]],HSTACK(EXPAND("",ROWS(Exclude),,""),Exclude)),,1), FILTER(CHOOSECOLS(u,3),(CHOOSECOLS(u,2)=F7)*(CHOOSECOLS(u,1)=$F$4),"not found"))
 
Upvote 0
Ignore post#19 it doesn't consider the Exclude list and this is shorter.
Book1
ABCDEFGHIJ
1
2
3Cost CodeEmployeeAllowancesColumn 4Cost Code
4124511068246Allowance 1Something1245EmployeeAllowances
5124511068246Allowance 2Something11068246Allowance 4
6124511068246Allowance 3SomethingEmployee No11068246Allowance 3
7124511068246Allowance 4Something11068246
8129710903301Allowance 1Something
9129710903301Allowance 2SomethingAllowance 1
10129710903301Allowance 3SomethingAllowance 2
11129710903301Allowance 4Something
12129710903301Allowance 5Something
13129710903301Allowance 6SomethingEmployee No
14129710903301Allowance 7Something10903301
15129710903301Allowance 8Something
16not found
Sheet2
Cell Formulas
RangeFormula
F9:F10,F16F9=LET(u,UNIQUE(VSTACK(FILTER(Table1[[Employee]:[Allowances]],Table1[Cost Code]=$F$4,""),Exclude),,1), FILTER(CHOOSECOLS(u,2),(CHOOSECOLS(u,1)=F7),"not found"))
Dynamic array formulas.
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

A
  • Solved
FILTER exclude results if a range contains any value/text
Replies
3
Views
472
Peter_SSs
Peter_SSs
H
  • Question Question
Based on Multiple Criteria Pick data from Total Sales Row
Replies
7
Views
330
NickFru
N
H
  • Solved
Dynamically pulling allowances and ignoring certain allowance for each employee
2
Replies
13
Views
595
hagia_sofia
hagia_sofia
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
155
Domenic
D
F
  • Question Question
Filter Formula W/ 3 Criteria (Leaving Blank will populate all)
Replies
2
Views
364
FiggyDad
F

Forum statistics

Threads
1,224,813
Messages
6,181,118
Members
453,021
Latest member
Justyna P

Share this page

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