SOLVED

# Help With Filter Function

Copper Contributor

# Help With Filter Function

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 (Copper Contributor)
Solution

# Re: Help With Filter Function

=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.

# Re: Help With Filter Function

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?

# Re: Help With Filter Function

@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.

# Re: Help With Filter Function

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;"

# Re: Help With Filter Function

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.

# Re: Help With Filter Function

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.

# Re: Help With Filter Function

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.

# Re: Help With Filter Function

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

# Re: Help With Filter Function

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.
1 best response

Accepted Solutions
best response confirmed by rttmmwv (Copper Contributor)
Solution

# Re: Help With Filter Function

=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.