SOLVED

Help With Filter Function

%3CLINGO-SUB%20id%3D%22lingo-sub-3119746%22%20slang%3D%22en-US%22%3EHelp%20With%20Filter%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119746%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20nothing%20too%20similar%20has%20been%20posted%20before%2C%20but%20I%20am%20struggling%20with%20a%20filter%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20sheets%2C%20One%20where%20data%20will%20be%20displayed%20to%20the%20end%20user%2C%20and%20a%20query%20that%20contains%20the%20information%20to%20be%20fetched.%20I%20am%20trying%20to%20have%20a%20section%20auto-complete%20with%20Employee%20Names%2C%20where%20employee%20cost%20center%20is%20XXXXX%20and%20Employee%20Job%20Title%20is%20EITHER%20Production%20Team%20Member%20OR%20Production%20Temporary.%20The%20filter%20is%20mostly%20working%2C%20but%20I%20am%20getting%20results%20for%20all%20job%20titles%20in%20the%20cost%20center%20(so%2C%20it%20is%20also%20pulling%20supervisors).%20I%20am%20using%20the%20below%20formula%20to%20accomplish%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFILTER(INDEX(HCData!A%3AG%2CSEQUENCE(ROWS(HCData!A%3AG))%2C%7B4%2C7%7D)%2C(HCData!A%3AA%3D%24A%2413)*OR(ISNUMBER(SEARCH(%22Production%20Temporary%22%2CHCData!C%3AC))%2CISNUMBER(SEARCH(%22Production%20Team%20Member%22%2CHCData!C%3AC)))%2C)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20originally%20tried%20using%20boolean%20argument%20for%20the%20OR%20condition%2C%20but%20that%20didn't%20work%20so%20I%20tried%20an%26nbsp%3B%20OR%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20I'm%20not%20able%20to%20provide%20the%20spreadsheet%20due%20to%20the%20nature%20of%20the%20data.%20My%20query%20columns%20are%3A%20%22Cost%20Center%22%20%22ID%20Number%22%20%22Job%20Title%22%20%22Name%22%20%22Shift%22%20%22Job%20Code%22%20and%20%22Job%20Code%20Abbreviated%20(conditional%20column)%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20any%20additional%20information%20would%20be%20helpful.%20I%20do%20have%20another%20cell%20using%20formula%3A%26nbsp%3B%3DFILTER(INDEX(HCData!%24A%3A%24G%2CSEQUENCE(ROWS(HCData!%24A%3A%24G))%2C%7B4%2C7%7D)%2C(HCData!%24C%3A%24C%3D%22Production%20Team%20Leader%22)*(HCData!%24A%3A%24A%3D'DAT1%20Org%20Chart%20(1st)'!%24A%2413)%2C)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20is%20only%20returning%20the%20correct%20job%20title%2C%20so%20I%20know%20that%20the%20issue%20lies%20somewhere%20in%20adding%20a%203rd%20condition.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3119746%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3119838%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Filter%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910745%22%20target%3D%22_blank%22%3E%40rttmmwv%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DFILTER(INDEX(HCData!A1%3AG50%3BSEQUENZ(ZEILEN(HCData!A1%3AG50))%3B%7B4.7%7D)%3B(HCData!A1%3AA50%3D%3C%2FSPAN%3E%3CSPAN%3EA13%3C%2FSPAN%3E%3CSPAN%3E)*((HCData!C1%3AC50%3D%22Production%20Team%20Member%22)%2B(HCData!C1%3AC50%3D%22Production%20Temporary%22))%3B)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20formula%20works%20in%20my%20spreadsheet.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3119862%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Filter%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3119862%22%20slang%3D%22en-US%22%3Eyou%20trying%20to%20say%20you%20have%20two%20excel%20sheets%3CBR%20%2F%3Eone%20the%20filtered%20one%20you%20have%20to%20present%20to%20end%20user%20with%20the%20following%20queries%3F%20ryt%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3131811%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20With%20Filter%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3131811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296787%22%20target%3D%22_blank%22%3E%40harshulz%3C%2FA%3E%26nbsp%3BYes%2C%20that's%20correct.%20I%20have%20a%20query%20on%20one%20sheet%20and%20will%20be%20filtering%20that%20data%20on%20the%20sheet%20presented%20to%20end%20users.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello All, 

 

I hope nothing too similar has been posted before, but I am struggling with a filter function. 

 

I have two sheets, One where data will be displayed to the end user, and a query that contains the information to be fetched. I am trying to have a section auto-complete with Employee Names, where employee cost center is XXXXX and Employee Job Title is EITHER Production Team Member OR Production Temporary. The filter is mostly working, but I am getting results for all job titles in the cost center (so, it is also pulling supervisors). I am using the below formula to accomplish this.

 

=FILTER(INDEX(HCData!A:G,SEQUENCE(ROWS(HCData!A:G)),{4,7}),(HCData!A:A=$A$13)*OR(ISNUMBER(SEARCH("Production Temporary",HCData!C:C)),ISNUMBER(SEARCH("Production Team Member",HCData!C:C))),)

 

I originally tried using boolean argument for the OR condition, but that didn't work so I tried an  OR function. 

 

Unfortunately, I'm not able to provide the spreadsheet due to the nature of the data. My query columns are: "Cost Center" "ID Number" "Job Title" "Name" "Shift" "Job Code" and "Job Code Abbreviated (conditional column)". 

 

Please let me know if any additional information would be helpful. I do have another cell using formula: =FILTER(INDEX(HCData!$A:$G,SEQUENCE(ROWS(HCData!$A:$G)),{4,7}),(HCData!$C:$C="Production Team Leader")*(HCData!$A:$A='DAT1 Org Chart (1st)'!$A$13),)

 

This formula is only returning the correct job title, so I know that the issue lies somewhere in adding a 3rd condition. 

9 Replies
best response confirmed by rttmmwv (Occasional Contributor)
Solution

@rttmmwv 

=FILTER(INDEX(HCData!A1:G50;SEQUENZ(ZEILEN(HCData!A1:G50));{4.7});(HCData!A1:A50=A13)*((HCData!C1:C50="Production Team Member")+(HCData!C1:C50="Production Temporary"));)

 

This formula works in my spreadsheet.

you trying to say you have two excel sheets
one the filtered one you have to present to end user with the following queries? ryt?

@harshulz Yes, that's correct. I have a query on one sheet and will be filtering that data on the sheet presented to end users. 

Would "SEQUENZ" be "SEQUENCE" and "ZEILEN" be "ROWS"? That is what I'm guessing based on google.... I don't have either of those exact functions.

I am also getting an error at "(INDEX(HCData!A1:G50;"
Sorry, I realized the error was coming from using semi-colon instead of comma. The Formula isn't giving errors now, but I'm getting "0" as the sole result.

@rttmmwv 

The formula returns the values from columns 4 and 7 of sheet "HCData". I deleted all the data in column 4 (Name) of sheet "HCData" and the formula returns only "0" for this column as you can see in the attached file. Maybe this is what happens in your file.

I tried with different columns and they're all returning 0. All of my columns have a few thousand rows of data, so there shouldn't be any blanks.

@rttmmwv 

Can you attach a file without sensitive data which shows the details that you can see in my attached files?

When I tried to recreate the error to send to you, it seemed to be working fine. I created a new Workbook, reran the query, and tried your formula gain and it seems to be working. Not sure what was causing the error, but it seems to be fine now.