Rumus filter tidak ada di excel

I have two sheets. One is for sales view, the other is for my view. The sales sheet has limited viewing rights and I use the =FILTER() function to pull through the information from my sheet to theirs. I use the filter function because the data (quote number) pulling though is either marked as "On Hold", "Released" or "Partially Released". I tried INDEX(MATCH) but it only pulls through the first quote where it encounters an on hold status and nothing further.

FILTER works perfectly for this as when I update the status on my worksheet it automatically updates on the sales sheet, though the problem now comes in that on the same row they need to comment on the quote status (why on hold).

Say the quote X was on A2 and their comment was on B2
and quote Y was on A3 and their comment was on B3

as soon as I mark quote X on my sheet as RELEASED it removes it from A2 (as the sales sheet only showes quotes on hold), moves quote Y up from A3 to A2 but, this is the problem that I need help with, the comment for quote X does not also dissapear. It stays in B2 because it is not linked to my sheet.

So after a while updating the quote's status none of the quotes and the comments match up.

When I tried formatting as a table I get #SPILL errors.

Is there a way to fix this or does anyone have another formula that I can maybe try?

Lenouis says:
November 28, 2022 at 11:13 am

This is a very useful Guide and I am comfortable using the FILTER formulae to get what I need most occasions. However, I am always stuck when trying to do the following:

I have one table with 5 columns. Col. A is used as the array to filter. Cols. D and E are the criteria to be used. The user chooses from the (data validation) list to enter the matching criteria in two separate cells. This works just fine when the user does enter BOTH criteria values

=FILTER(Table1[Project Name], (Table1[Priority]=L2)*(Table1[Progress Indicator]=N2), "None")

I can enter the two criteria for example: Priority =High Progress =Delay

But what if I just put in a criteria to one of those cells and leave the other blank. I want the filtered list to return the filtered results that match that one criteria and ignore the cell value that I have not entered a match value.

I want to get some results if I choose for example: Priority = [value not chosen from list] Progress =Delay

This returns "None" (i.e. FALSE) when really I want it to return all filtered results where Col. E = Delay.

I have tried numerous IF formula and nested the FILTER formula but nothing works. Please note that I do not have an "ALL" value either in any columns or in the data validation lists - so effectively I am wanting the formulae to 'USE ALL' if either the criteria cells have no values entered.

TraceyAnn Smith says:
November 28, 2022 at 5:07 pm

I'd tried the Advanced filter function before I asked for help as well. That doesn't "auto update" each time the source data changes - I have to re-apply the filter each time the source data changes.
I want to be able to load ALL the data into one tab (The FULL DATA), and then (for each teacher) create another TEACHER tab that pulls all the rows for each of their list of students (where the list is stored on ANOTHER tab) and shows all the rows for each student. AND, the next week, when the grades are updated, to be able to updated the FULL DATA tab with the most recent info and have all the TEACHER tabs automatically show the new updated rows.

So to accomplish =FILTER('FULL DATA'!A2:AG12000,'FULL DATE'!A2:A12000=991000303)
EXCEPT THAT instead of a single ID (991000303) I can filter all the rows for a set of IDs, a la:
=FILTER('FULL DATA'!A2:AG12000,'FULL DATA'!A2:A12000='LIST OF STUDENTS IDS'!B3:B45)

I know the advanced filter will let me do the range...but then when the data changes, I have to re-apply the filter. Since I'm doing this for multiple people (who all need to look at a different set of IDs), I don't want to re-apply anything. And, the OR function is difficult to create and maintain when there are 25-45 different IDs each teacher would need to look at (hence the reason I'd like to have the IDs in a range of cells).

kftgr says:
November 30, 2022 at 11:08 pm

TraceyAnn,

I know that you've said you solved it, so I'm just leaving this here in case anyone else wants to try a different solution. This uses power query:

Let's say you have a named range called FULLDATA with columns that you want to filter on called FD_criteria1 and FD_criteria2.
Have another named range called CRITERIA with the columns criteria1 and criteria2, each listing the multiple values you want to include.

The filtered output can be found by having a query with the following code:

// beginning of query
let
// get our source data from named range FULLDATA
Source1 = Excel.CurrentWorkbook(){[Name="FULLDATA"]}[Content],
// turn the first row into headers and call the resulting table "FullDataTable"
FullDataTable = Table.PromoteHeaders(Source1, [PromoteAllScalars=true]),

// get the criteria data from named range CRITERIA
Source2 = Excel.CurrentWorkbook(){[Name="CRITERIA"]}[Content],
// turn the first row into headers and call the resulting table "CriteriaTable"
CriteriaTable = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),

// keep only rows from FullDataTable where the FD_criteria1 column value matches CriteriaTable's criteria1 column values
// this filtered dataset is called Filtered_On_Criteria1
Filtered_On_Criteria1 = Table.NestedJoin(FullDataTable, {"FD_criteria1"}, CriteriaTable, {"criteria1"}, "New_Column_Name1", JoinKind.Inner),

// keep only rows from Filtered_On_Criteria1 table where the FD_criteria2 column value matches CriteriaTable's criteria2 column values
Filtered_On_Criteria2_also = Table.NestedJoin(Filtered_On_Criteria1, {"FD_criteria2"}, CriteriaTable, {"criteria2"}, "New_Column_Name2", JoinKind.Inner),

// the two previous steps created additional new columns, so remove them
OutputTable = Table.RemoveColumns(Filtered_On_Criteria2_also,{"New_Column_Name1", "New_Column_Name2"})
in
OutputTable
// end of query

Harit Babulal Dobariya says:
November 1, 2022 at 8:47 am

Hie,

Need experts help!

I have simple data but complex calculation.

From Date To Date Level Size Bands Local Regional National
01-01-2022 31-12-2022 Premium 0 to 500 gm 25 39 60
Advanced 0 to 500 gm 25 39 60
Standard 0 to 500 gm 31 40 61
Basic 0 to 500 gm 37 45 65
All Levels 500gm to 1 kg 13 17 25 (Fee per Each 500gm upto 1 kg)
All Levels 1 kg to 5 kg 21 27 33 (Fee per Each kg upto 5 kg)
All Levels 5 kg + 12 13 16 (Fee per Each kg after 5 kg)

Requirement is to calculate the total fee for specific zone (Local/Regional/National) with current level (Premium/Advanced/Standard/Basic) for total weight of item ordered in particular date range.

Example: If someone ordered an item weighing 4.5 kg in total on 23-04-2022 which is to be delivered at national level and level is advanced, total fee will be - 60(0-500gm) +25 (500-1kg) + 33*4 (each kg upto 5 kg) = $ 217.

New values of fees will be there as per dates ranges. Size bands can also vary based upon dates. Levels & zones are constant. Fees to be calculated based on ordered date. Cannot use VBA. Using Online excel. Formula will be more suitable.

Joseph says:
October 27, 2022 at 4:35 am

Hi,

First of all, thanks a lot for all this information, but I need to go a little bit further than this article. I need to filter the same data but in multiple "tables" (side by side). I want to start the second "table" with the end of the data of the previous "table".

Each "table" must contain 81 lines and 6 columns. I am able to create 1 table due to the information on this page, with the combinaison of Index, Sequence and Filter. But once I limit the filter with the Index formula, I can't start a new "table" with the last data used in the previous "table".

For example :

Primary data Table 1 Table 2
A B C D E F G H I
Name Group Wins Name Group Wins Name Group Wins
Aiden A 0 Andrew C 4 Charlotte B 2
Andrew C 4 Betty B 1 Mason A 4
Betty B 1 Caden C 2
Caden C 2
Charlotte B 2
Emma C 0
Isabella A 0
Mason A 4

Is there a solution to this problem?

P.S. My primary data is listed in a various (pretty high) number of lines but is fixed with 6 columns. And I want to display the filtered info side by side with a fixed number of lines.

Thank you,

Joseph

Marco says:
July 16, 2022 at 1:55 pm

Amazing forum, thanks for all the helpful tips!

I keep running into a condition which I can't solve...

I'm trying to use the FILTER function (which works fine) but I nest it into a COUNTA because I want to count the number of items inside the filtered result list (the list is a list of alphanumeric codes hence my attempt to use COUNTA).

COUNTA( FILTER( array, criteria, 0))

The problem only occurs when there is a null result, it always returns 1 (instead of blank or 0).

I have also tried using a COUNTIF( FILTER( array, criteria, 0) , TRUE) in an attempt to count the TRUE only, but it won't let me enter that formula at all (true criteria at the end seems to not be accepted no matter what I try, e.g. "1", "True", "False", etc...)

I believe Excel is trying to tell me that I should not be using FILTER when I want to count like this....

I'm sure many people are already solving this using another method, I just haven't figured it out yet.

Any help?

Tom says:
June 10, 2022 at 9:36 am

Thanks for your reply. I get that you can use the Excel filter tool to do that, but then you have to manually enter/clear your filter each time you want to search. I work with large glossaries, so I just want to search for a term in one column (e.g. in an activex text box), and then it will apply the filter in real time as I type. I can do this using VBA (see working code below), but I'd like to do this without having to save and share macro-enabled workbooks with colleagues.

I hope that makes sense!

How it works in VBA:
I insert an activex text box and link it to cell C1. I then assign the below macro to it. When I enter text in the box, it is replicated in cell C1 and the column is filtered based on the text in cell C1.

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Monographs").Range.AutoFilter Field:=1, Criteria1:="*" & [C1] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Enrique says:
April 18, 2022 at 11:29 am

Alexander, thank you for your response.

I should explain better. If I had an inventory of cars at a dealer ship in a sheet with Make, model, type, etc... would I be able to create a filter where if I have 2 "And" values. Make and Type.

example:=FILTER(invo,(invo[Make]=F2)*(invo[Type]=F3))

Table=invo,
*Make *Model *Type
Chevy-Malibu-sedan
Chevy-1500-pickup
Chevy-2500-pickup
Ford-explorer-suv
Ford-F150-pickup
Ford-F250 pickup
Toyota-Corola-Sedan
Toyota-Tundra-pickup
Toyota-Tacoma-pickup

If I only place 1 value in the filter such as "pickup" it will give me the a list of pickup in my inventory with mix of all 3 makes.

But, if I type 2nd value of Make, I can narrow to all the "pickup" And "Chevy"

Currently it only filters if both values are added.

When running a Query in google sheets this seems to be the default "And" behavior but If I leave a value blank under the excel =filter it wont give results. If i use the "Or" "+" i get a mixture which can be used for certain sheets but not to narrow down inventory search.

Jeff Small says:
March 7, 2022 at 4:24 pm

Hello
Thanks for publishing such a valuable article, but I still have a question I hope you can help me with.

If we use the table of data you have used in this article (Name, Group, and Wins), what I want to do is filter out Group B and also determine the largest value for Group B. In other words, I want the filter to be able to determine that the largest value in Group B is 3 and that it belongs to Oliver.
When I set my filter function to filter for Group B AND the largest value, I get an error as my filter is finding the largest number in the Wins column (which is 4 from both Groups A and C) and it does not correspond to Group B, therefore, the error.
I want to keep the filter dynamic and thus wanted to use the filter function, but I can’t seem to make this work even though it sounds simple. Essentially, I need the filter function to look only at the number of wins in Group B and then determine the largest value of wins and then spit out Name, Group and the number corresponding to the largest wins.
Any help would be greatly appreciated. Thanks for your time.

Jack says:
April 30, 2021 at 2:48 pm

Hi, Excellent info on the filter function... saved me many weeks of work!

I wonder if you could help me on this:

Am applying a filter on a price list, to return a set of products based on the type of category - and this works fine. But now I would like to eliminate rows which have become "obsolete" since price has changed. For example I have this data set:

PRODUCT - PRICE - LAST PRICE CHANGE
Coffee Brand A - $5 - 1/1/2020
Tea Brand X - $1 - 5/12/2020
Coffee Brand B - $4 - 1/5/2021
Coffee Brand A - $6 - 4/30/2021

I would like my output to IGNORE the first row, dated 1/1/2020 since the price for that brand (Coffee Brand A) has now changed on 4/30/2021.

Is there a way to achieve this dynamically? I would need to include this as part of the FILTER function, since the table above would have been "built" by using a filter on the category called "Tea&Coffee".

Thank you!

Mekan says:
January 27, 2021 at 7:49 pm

Hi Alexander,

Thanks for your comments and help to master excel. I was able to figure this problem out using IF statements together with FILTER function. Just in case anybody came across with a similar situation below how I solved the problem:

Step 1.: I created a table with color and prices for T-shirts (i.e. I3"BLUE", J3"12"; I4"RED",J4 "10"; I5"GREEN", J5"5" AND I6"ALL COLORS", J6"0")

Step 2.: I created a two data input table 1 for Price (B1) and one for Color where you can select individual colors and "ALL COLORS" from the drop down list (I used Data Validation- List).

Step 3. I created a below formula in cell A4:

=IF(B2"ALL COLORS",FILTER(I3:J6,(J3:J6>B1)*(I3:I6=B2)),FILTER(I3:J6,(J3:J6>B1)))

Basically the logic of formula goes as the following:

If color is not selected as "ALL COLORS", then filter table based on Price and Color, else filter based on Price only.

Now I can filter T-shirts based on individual colors as well as price only. I know it might sound simple but it took me few days to research and think about it.

Filter di Excel ada dimana?

Kenapa filter tidak aktif di Excel?

Ctrl apa untuk filter?

Bagaimana Cara Clear Filter di Excel?