May 5, 2006

Attached I have a spreadsheet with two columns. Both are dates. One is Due Date and the other is Completed Dates. Each row represents a WO (each WO has a due date and complete date). This is what I want to extract:

- I want a count of how many WO's there are in the spreadsheet to start with
- then I want to to be able to apply this fillter = Complete Date is equal to or less than Due Date (basically, I want to see how may WO's where completed after the due date).
- then I want the spreadsheet to automatically calculate the count of WO's with the filter applied

Mar 23, 2014

We have a spreadsheet that pulls data from various other spreadsheets that contain information on current year data, budget year data, and last year data. We need to pull from the current year spreadsheet information each month on the inventory count on hand.

We need one formula in one cell of the main spreadsheet that will know to pull from the appropriate month column of the current year spreadsheet based on the particular month we are running the report for.

In other words, we are not wanting to need to change the formula each month, but to write it such that it will know by our indicating in the main spreadsheet which month it is (which is always indicated in Cell B14).

In the main spreadsheet, the cell containing spreadsheet information on where to find the current year data is \$D\$2.

Advanced Filter Code - Criteria Range More Than 1 Row Breaks Filter

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

Jun 14, 2009

Can anyone explain why the unique filter does not produce a unique result - sample attached?

Advanced Filter To Filter A List Of Data

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

Advanced Filter :: Filter Data Between Two Dates

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 .

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?

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?

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.

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?

Advanced Filter Not Filtering In UDF?

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

[Code]....

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
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
"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?

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.

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.

Feb 9, 2010

This code works, but I would like to cut (remove) the records from the original record list.

("H1:H3"), CopyToRange:=Range("J1:O1"), Unique:=True

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.

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.

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.

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.

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.

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?

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
"M:P"), Unique:=True
Range("L1").Select
End Sub

Advanced Filter Vba - Workbook Name

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"). _
CopyToRange:=Range("B8:R8"), Unique:=False
Range("B8:R8").Select
Selection.AutoFilter
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

Advanced Filter With AND + OR Conditions

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

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}

Advanced Filter Macro Does Not Work?

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

Advanced Filter To Get Rid Off The Duplicate Lines

Feb 11, 2009

I have a large spreadsheet of data, with various columns with different bank names, the amount (column H) each bank holds plus loads of other columns. Some of the banks are duplicated, so in a way I want to do an advanced filter to get rid off the duplicate lines, except that I need the amounts (column H) summed.

Advanced Filter For <> Several Text Strings

Jan 8, 2010

i have a lot of data which i am going to be running an advanced filter on. One of the criteria i am trying to filter for is a list of text values that i want to make sure a row does not contain. Lets say for example I have a list of 2 text values in column "symbols" that i would like to omit in my filtered results, "abc" and "xyz".
As of now I can only set up a criteria to omit one of those.

Symbol
<>abc

this works right now.

But if i try to put <>xyz in the cell immediately under there, I thought its supposed to be filtering for symbol that does not equal abc or xyz, but instead it doesnt filter anything out. I think it may be a syntax error or something else that I am doing wrong. Can anyone point me in the right direction?

Ideally, I would like to be able to filter out a list of text values that I keep in another column. Like lets say today I wanted to omit "abc" "xyz" and "lmnop" and I would have the advanced filter criteria just reference that list of values as what I would like to filter out. Except there would probably be a list of at least 20 to 30 values, that change over time, so it would be much easier to just keep it all neat in one column that I can edit as I need to.

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?

Dec 5, 2008

I have an Excel sheet with a few thousand rows which I would like to filter by a column with the name TrackingID. This column contains values like:

12AA1
23452BA2
234AA1
345635CA2
...

I would like to filter this column by the third character from the end (which is always a letter from the range [A-Z]). In the example above, this would be:

A
B
A
C...................

Jun 19, 2009

I'm trying to automate advanced filter but can't get past having just 1 critreria.
I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page. I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).

so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre. Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).