Using Advanced Filter In Combination With Other Filters
Jul 10, 2009
I am having some problems trying to filter a list to display exactly what I want to see. The list has one column of part numbers, a second with due dates, and then another with quantity.
I want to use an advanced filter on the part numbers to only look at unique entries. Then I want to filter that list using a custom filter on the due dates to only view those due within a certain period. So ultimately I want to view only unique entries due during a given period.
I am able to apply one filter, but when I go to apply the second, the second filter removes the first. For example once I have filtered out duplicates, when I try to filter based on date all of the duplicates return.
View 7 Replies
ADVERTISEMENT
Apr 27, 2009
using VBA I have a control sheet which summerises variouse counts & totals of data held on a detail sheet. Bu using filters and counting the visable rows.
Statistics on 50 columns of data held in several thousand (rows) mixed around eight business regionsheld in first column.
I could determin the number of affected rows by using Tick boxes on the control sheet and applying filters to the detail records
A) checkbox indicates if I need filtering on the type of data in my detail sheet and apply the filter
Selection.AutoFilter Field:=XX, Criteria1:="Y"
B) Because I could not have more than two criteria on an autofilter column I resorted to using Advanced Filter on the column with the Business UNIT's,
I Create a range write the criteria of the records to be filtered into the range, then apply an advanced filter using that range.
Both of these work well indevidually, but I am getting inconsistant results when I mix them
using the autofilter route I can select multiple tick boxes and the output is correct, and using the advanced filter I can select any combination of business units and the output is correct, however I cant get them to work together
View 4 Replies
View Related
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
May 29, 2007
I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.
To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.
The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!
View 3 Replies
View Related
Sep 17, 2008
I can't seem to get the hang of advanced filters with multiple criteria. BTW, I have no problem solving for a single criteria. I've found many instructions on how to do it, but zero specific syntax examples, and I'm obviously doing it wrong. For example: ....
View 9 Replies
View Related
Jul 4, 2014
I'm creating a database that contains a columns of hyperlinks and background color.
I've created code on Excel 2010 with advanced filters to copy selected rows to another worksheet. The extract works fine and those cells with hyperlinks and various color codes from the original database "appear" to have been extracted correctly.
However, a closer look shows the hyperlinks on the target worksheet are just blue underlined texts...with the links no longer working. Is there a way for me to correct this?
View 9 Replies
View Related
Jul 18, 2014
I have created a spreadsheet that will show me where people are working on what day, etc. however i want to be able to filter by week to create a list of say 2 particuar shifts - in this case "syl ld" and syl n so that the spreadheet would show the people who are working these shifts and I could print out. I have tried Multiple Filters and Advanced search but cannot achieve what I require
View 3 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
Jun 14, 2009
Can anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies
View Related
Jan 14, 2010
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
View 9 Replies
View Related
Mar 15, 2007
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
View 5 Replies
View Related
Apr 8, 2014
I have a large Excel with details of 1,000 staff
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James
Wilkonson, Paul,
etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
View 11 Replies
View Related
Aug 5, 2014
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
View 8 Replies
View Related
Jul 25, 2006
I have a file that has a column of dates. I would like to use advanced
filter to filter anything with a date that is <today()-1 but I can't seem to
make the fomula work. I can make it filter on =today()-1 but when I use the
< it doesn't calculate today's date and gives me nothing.
View 9 Replies
View Related
Sep 8, 2009
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
View 9 Replies
View Related
Dec 17, 2012
why the Advanced Filter works in the macro but not in the UDF?
Code:
Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name
Dim c As Range
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
[Code]....
View 2 Replies
View Related
Mar 24, 2007
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
View 9 Replies
View Related
Oct 28, 2008
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
View 9 Replies
View Related
Aug 23, 2009
I have a huge list (over 900,000 rows) of text codes (e.g. ABC-12345) on Column A Spreadsheet 1 and would like to remove the ones that are not included in Column A Spreadsheet 2.
I changed all of the text on Column A Spreadsheet 2 to have the in front of it (e.g. ABC-1234) and tried to run the advanced filter but it does nothing.
View 9 Replies
View Related
Feb 9, 2010
This code works, but I would like to cut (remove) the records from the original record list.
Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("H1:H3"), CopyToRange:=Range("J1:O1"), Unique:=True
View 9 Replies
View Related
Jan 17, 2009
I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.
resource1resource2resource3 XResources YResources
Jeff X resource2
John X X resource2 resource3
Jim Y resource1
I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.
View 6 Replies
View Related
Feb 15, 2010
i am trying to work out the coinage for wages ie. 5546.75 needs to brake down into dominations ,notes and change i live in south africa so we use the desimal system. i have tried to use floor and mod as formulas but at some points it returns an error or incorect result.
View 8 Replies
View Related
Jun 21, 2006
I am trying to create a function using vba and the advanced filter function.
The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.
This is my code.
Sub FilterCGML()
Dim rngToFilter As Range
Dim rngFilterCriteria As Range
Dim LastRow As Long, LastCol As Integer
With Sheets("Main FTE")
Range("b2000").Value = "Cost Code"
Range("b2001").Value = "5*"
It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.
I know how to find the last column and last row by using
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column
But I don't know how to modify my code to use these.
View 4 Replies
View Related
Jul 13, 2006
I am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.
View 9 Replies
View Related
Dec 4, 2006
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
View 9 Replies
View Related
Jan 17, 2007
I want to create an advanced filter criteria along the lines of
=AND(col1>begindate,col1<endate)
where 'col1' is the title in the cells at the head of the appropriate columns in the data range (B1), criteria range(F1) and extract area range (I1), and 'begindate' and 'enddate' are named single cells.
Why doesnt this work?
View 8 Replies
View Related
Mar 9, 2007
i am trying to use the autofilter command to paste 4 columns else where on the page using unique = true. this only will run once. when i try to run it again everything is blank. please see code below.
Sub copyindex01()
Columns("L:O").Select
Range("O1").Activate
Selection.Delete Shift:=xlToLeft
Range("G2").Select
Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"M:P"), Unique:=True
Range("L1").Select
End Sub
View 4 Replies
View Related
Apr 2, 2007
I created an advanced filter macro which analyzes the "Orig_Appmt_Data" dataset from the "Summary" worksheet and provides the resulting analysis on the "Effective Rate" worksheet. However, when I change the name of the workbook, the macro fails. I was hoping someone could provide some assistance in helping adjust the vba code to take into account the subsequent changes in workbook filename. Here is the
Sub ER_Analysis()
'
Range("B8:R109").Select
Selection.ClearContents
Columns("P:R").Select
Selection.EntireColumn.Hidden = False
Range("Q5").Select
Range(" Conversion 3-28-07.xls'!Orig_Appmt_Data"). _
AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Q5:Q6"), _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R8").Select
Selection.AutoFilter
Range("B8:R87").Sort Key1:=Range("P8"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=1, Criteria1:="<>", Criteria2:="<> TOTAL"
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.Font.Bold = True
Range("P8").Select
End Sub
View 3 Replies
View Related
Nov 17, 2007
I am trying to create my critera section for an Advanced Filter, but it seems like Auto Filter uses each row as a specific filter.
i.e. If I need to filter all the "2"s in column A, but both the "1"s and "2"s in column B, I need to create two different filters like this:
Column A Column B
2 1
2 2
Instead of just:
Column A Column B
2 1
2
Is there any way I can use auto-filter without having to put in every possible combination of my criteria in as different filters? Auto Merged Post;I did try creating columns next to each other, but that seems to only work as an "AND."
Per my example, I need to return everything with column A = 2, and column B = 1 or 2.
Except in my actual spreadsheet, it's more like where A = {1,2,3,4,5} and B = {0,9,8,7,6} and C = {a,b,c,d,e}
View 7 Replies
View Related
Feb 15, 2013
I've written a macro in an attempt to automate the advanced filtering in Excel.
VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....
View 5 Replies
View Related
