Formula To Find The Filter Criteria
Dec 10, 2005
I have a worksheet which has in column A2:A9 a label and in column
B2:B9 a value, like so:
A1="LABEL" B1="VALUE"
A2="LabelGreen" B2=452.47
A3="LabelBlue" B3=-87
A4="LabelRed" B4=9
A5="LabelGreen" B5=-7
A6="LabelBlue" B6=1888.97
A7="LabelRed" B7=144
A8="LabelGreen" B8=-0.02
A9="LabelBlue" B9=87002
I use the autofilter on column A to select only certain labels.
in cell D1 I use the subtotal formula to give me the sum of all
filtered values.
D1=SUBTOTAL(9,B2:B9)
This works fine, for example, if I filter on column A for "LabelGreen",
the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45.
What I am trying to do now and what I have not yet found a solution for
is:
In cell C1 I want to display the criteria I have filtered for.
For example, when I filter column A for "LabelGreen", I want
"LabelGreen" to be displayed in cell C1.
I was thinking something similar to SUBTOTAL might do the trick, for
example:
C1=SUBFIRST(9,A2:A9)
But this only gives me (and I am not surprised) an error "#NAME?"
View 12 Replies
ADVERTISEMENT
Jun 28, 2014
I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.
BillT Doc.ItemQty
F11231012.00
F2123205.00
S1123105.00
RE321202.00
F2321108.00
F2321201.00
RE321203.00
RE999808.00
F27771001.00
RE7771001.00
I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.
So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.
I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.
View 8 Replies
View Related
Apr 10, 2008
Im looking for a way to select between multiple choices linked to one criteria. Eg criteria beeing Chain x Article 1, which should give me the option to show and select from multiple unique values linked to that criteria (in attached example values 1 or 30 in column AQ marked in yellow). If possible solution should be formulas/functions.
View 2 Replies
View Related
May 1, 2014
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
View 4 Replies
View Related
May 20, 2014
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies
View Related
Mar 27, 2014
I'm trying to find the correct formula to sum text that meets two criteria. If C2:C1023 is Equal to TEXT from J8 and F2:F1023 is equal to TEXT J4 add up the total times J4 shows up in C2:1023
=SUMPRODUCT(Sheet3!C2:C1023=J8,Sheet3!F2:F1023=J4,Sheet3!C2:C1023)
View 11 Replies
View Related
Apr 1, 2014
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
View 1 Replies
View Related
Feb 26, 2012
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
View 3 Replies
View Related
Apr 24, 2014
i have the following code, what it does is, it locate those empty cells in column M and insert the formula "=TODAY()". What i need the code to do is only insert to the empty cells in column M if there is a value(as long as is not empty) in the reference cell of column E.
VB:
VB:
Private Sub CommandButton3_Click()
Dim wks As Worksheet
Dim rng As Range
[Code].....
View 7 Replies
View Related
Feb 10, 2008
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies
View Related
Dec 12, 2009
I have a filter and need 2 (two) criteria.
1) In a (long) list of dates I need to see only the last month
2) From that selection (last month) I need to filter a date.
Note: I would like the filter to only show the last month as the list becomes very long.
Current solution
Column A has all the dates
Column B is a copy of column A
I use a small macro
View 6 Replies
View Related
Dec 13, 2011
Currently I got 1 vba coding which only filter with 3 criteria. Can I alter the vba to have 4 to 5 criteria in the filtering? Below is my VBA coding. Let say I want to add 1 more filter for column C2...
Range("A2").Select
Selection.Sort key1:=Range("E2"), Order1:=xlAscending, key2:=Range("B2"), Order2:=xlAscending, key3:=Range("A2"), Order3:=xlDescending, Header:=xlYes, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
View 3 Replies
View Related
Jun 7, 2007
I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc.
I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet.
View 9 Replies
View Related
Oct 29, 2013
In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies
View Related
Jul 10, 2012
How to give a cell link instead of any value for filter criteria, For example I need the data between two date range, the first date is in cell A1 and second date is in cell B1 by using macro. I have tried by using below code also but after filter all data hiding.
Dim a As Date
Dim b As Date
a = Range("a1")
b = Range("b1")
Rows("2:2").Select
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=a", _
Operator:=xlAnd, Criteria2:="
View 1 Replies
View Related
Nov 18, 2013
I have a Excel sheet which has a Product type values ASP,VSP etc in the first column. The second column has values Product Sub Types,which has values abc123, abc123456,abc123789, def123,def123456,def123789.The third column ServId's abc01,abc02,def01,def02.
If the Product Type is ASP,then I need values abc123,def123
But if Product Type is VSP, then I need values abc123
abc123456;abc123789
def123
def123456;def123789
View 4 Replies
View Related
Jun 13, 2007
I have a dataset, which I would like to sort on multiple criteria. The code I have is :
Dim Lst As Long
Dim Hdr As Range
Lst = Range("A65536").End(xlUp).Row
Set Hdr = Range("A6:AD" & Lst)
With Hdr
.AutoFilter
.AutoFilter Field:=9, Criteria1:="Complete" 'Match Status
.AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status
End With
Field 9 Match Status will always be Criteria "Complete"
Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.
This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".
View 9 Replies
View Related
May 5, 2009
I would like to filter a row using the following criteria: date should be < or = todays date.
I have tried the below but it doesn't work
Selection.AutoFilter Field:=11, Criteria1:="
View 9 Replies
View Related
Aug 20, 2008
I am using the code below to coax Autofilter into letting me use three criteria. I can hard code the values in for two of the cells (B6 and C6 in this case) but for it to be of any use I need the array to use the actual values found in those two cells.
With Worksheets("Sheet2")
Cells.Autofilter field:=1, Criteria1:=Sheets("Sheet1").Range("B1").Value
Cells.Autofilter field:=6, Criteria1:=Array("2880", "2879"), Operator:=xlFilterValues, _
Operator:=xlOr, Criteria2:=Sheets("Sheet1").Range("D6").Value
End With
How should this be setup to allow the array to read the two actual cell values?
View 2 Replies
View Related
Nov 8, 2006
I have a table that I filter out each buyer
Buyer Item Weight
Smith PartA 1000
Smith PartB 1000
Jones PartA 1000
Jones PartB 1000
Fred PartC 900
So when I filter out Jones, it would look like this
Buyer Item Weight
Jones PartA 1000
Jones PartB 1000
With Jones cell reference A4 & A5. Can I link a cell to the 1st visible cell below the autofilter? This cell would change each time I selected a different buyer.
View 3 Replies
View Related
Jul 11, 2007
What i need to do is basically delete every row in my spreadsheet that contains the criteria "L-0.###" in said column. by ### i am referring to 3 decimals, such as:
L-0.250
L-0.000
L-0.555
and so on. you get the idea.
with the below program i got it working so i can type in:
L-0.***
and have it sort out everything proceeding L-0. ...problem is i need to keep the data such as this:
L-0.53
L-0.00
L-0.0
or basically everything not 3 decimals in with a head of L-0.
here is the macro i'm using:
Sub DeleteRowByString()
'
' DeleteRowByString Macro
' Macro recorded 7/10/2007 by Khenzel
'
' Keyboard Shortcut: Ctrl+Shift+S
'
'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that should be removed from matching columns. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="Ferguson Enterprises, Inc.", Type:=1 + 2)
'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found. (Ex. A=1, B=2, Etc.)", Title:="Ferguson Enterprises, Inc.", Type:=1)
if possibly a modification in my coding to make this work, or simply a criteria i can type in to make it happen.
View 4 Replies
View Related
Oct 24, 2007
Anyway, in my workbook I have a master sheet which contains all the information entered (this master sheet will continue to grow as more information as entered). I want to create a userform to allow someone to search the data by selecting a date range (from/to) and a criteria (Pending/Settled) and for the results to be copied into a new sheet (simply I don't want the raw information tampered with in an unregulated way).
View 3 Replies
View Related
Jun 1, 2008
i want to display the results of one criteria using VBA codes instead of just filtering one by one to display the result and collate it. I have attached the sample. I want to find the "subcodes" of name "kathy". to be able to do that, i filter "kathy" to display all the "codes" then filter all the "codes" to find the "subcodes". is there vba code for this one so that when i input the name, all the subcodes will be displayed?
View 6 Replies
View Related
Jun 18, 2008
I am trying to run a macro to filter data based on 3 criteria. i want to filter column c to show rows with "Application", "Application / Web", and "Application / Database" which is referenced in the criteria range stated below.... Sheets(" Validation").Range("D95:D97"). for some reason it does'nt pick up the answers when i know that they exist in column c. could it be that they are formulas populating column c?
Sub Macro3()
Range("c4:c200").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Validation").Range("D95:D97")
End Sub
View 3 Replies
View Related
Apr 29, 2014
I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
View 3 Replies
View Related
Apr 21, 2014
i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21
Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.
for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.
in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.
View 14 Replies
View Related
Mar 5, 2014
Can I use vba advancedfilter to work with more than one criteria?
I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:
[Code] ......
I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...
The months are numerical in those fields - 1 through 12.
Can I add to the present filtering line or do I need to then create an additional filter?
View 6 Replies
View Related
Oct 21, 2008
I have a range of data (up to 20,000 cells) that has up to 30 different entries. I need to apply a filter to this data to filter out all but 5 of these.
View 5 Replies
View Related
Feb 15, 2010
I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.
View 9 Replies
View Related
Oct 25, 2011
I'm trying to use an advanced filter to filter a large data set using several parameters. I had this worked out before but there have been some parameters added that have thrown my filter off balance and I'm struggling getting it back.
Here's the criteria I need to filter
Column 1:
85
Column 2:
3
Column 3:
BU 1
BU 2
BU 3
BU 4
Column 4:
BA 1
BA 2
BA 3
BA 4
BA 5
BA 6
BA 7
Since I have an uneven list of criteria, I'm struggling to figure out the layout with the 'AND' condition. I know I can copy the 85 and 3 figures to each line but when I have 4 figures in column 3 and 7 figures in column 4, how can I create the 'AND' condition for all these criteria?
View 2 Replies
View Related
