VBA - Advanced Filter / Find Row Items To Remove Based On Multiple Criteria

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


ADVERTISEMENT

Advanced Filter With Multiple Criteria?

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

Multiple Advanced Filter - 3 Criteria

Feb 7, 2012

I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:

Col A Col B
Name Animals
Smith Cows, Pigs, Sheep, Horses
Jones Sheep, Pigs, Chickens, Geese, Alpaccas
Price Cows, Sheep, Pigs, Chickens, Rabbits
Williams Cows, Chickens, Horses, Alpaccas, Pigs

I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.

The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.

View 2 Replies View Related

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

View 4 Replies View Related

Advanced Filter Help To Remove Header

Jan 23, 2008

Sub filtertest()
Range("C17:L33").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("C13:C14"), CopyToRange:=Sheets("Combined Summary").Range("B7")
End Sub

This works fine - it filters the data in C17:L33 on the source sheet and then copies it to the combined summary (beginning in cell B7) but when it copies the data it also copies the header row - i only want to the data to be copied? - how can i amend the above??

Also - there will be a few tables i'll be amending the above code to work on as i'll be pulling them into 1 summary - how can i amend the B7 section so that they paste into the next free row - because at present they will just past over one another?.........

View 9 Replies View Related

Advanced Filter - Extract List Of All Items But Only With Latest Date Of Each

Jan 20, 2012

What criteria can I use to filter a list on and item and the latest date?

I need to extract a list of all items but only with the latest date of each ...

View 3 Replies View Related

Remove Duplicates :: Advanced Filter, Unique Records Only

Jan 14, 2010

I'm having a problem deleting duplicates from list in excel. Iíve attached a sample. Iíve tried the following:

1-Advanced Filter, Unique Records Only

2-Remove Duplicates function in Excel 07.

3-Pivot Table

4-Colour Conditional Formatting, sorting by colour

5-B2=IF(A2=A3,ĒDupĒ,ĒNot-DupĒ). The entire column returns ďNot-DupĒ

6-Iíve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ĎNumericí Keypad to appear in the ĎFindí Function.

[url]

View 14 Replies View Related

Advanced Filter List Criteria

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

Advanced Filter With Double (or / Or) Criteria

Jan 11, 2013

as u can see on picture I have some data and i want it to filter with "debet" and "credit",while debet can be zero or number x when in the same time credit can be zero or the same number x so when entering number x meaning e.g. 500 i get to rows

first
debet = 0 and credit = 500

second
debet = 500 and credit = 0

while running advanced filter i was getting nothing because i couldn't write what i needed

View 5 Replies View Related

VBA Advanced Filter Criteria Range

Jun 12, 2008

When doing advanced filter in VBA, is there a way to set the criteriarange, without having actual cells on a worksheet with the criteria in?

I've tried criteriarange:=Array("Currency", ws.Name), but it didn't like it.

View 9 Replies View Related

Advanced Filter With Not Equal To Criteria

Nov 19, 2007

I'd like to ask if there is anyway to use an advanced criteria with a NOT EQUAL operator.

I have a list that contains about 50 different data values that I want to filter but I want to restrict the list to not contain four different data items. Clearly, autofilter did not work as I can only specify two conditions in the custom filter. I want to use advanced filter instead but this only tests for equality.

View 3 Replies View Related

Set Criteria And Run Advanced Filter Not Working - Only Heading Appear

Jun 16, 2014

I think to run one report. First sheet put options and copy to data page as criteria, and run advanced filter, it only shows heading.

VB:
Sheets("Report").Select
Range("23:350").Delete
Sheets("Details").Select
Range("w8:ae9").Copy
Range("w12").Select

[Code] ......

View 2 Replies View Related

Advanced Filter With Function As Criteria Range

Feb 27, 2008

I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2).
I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like:
If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.

I need to check all 2500 values in all 6000 rows.

I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.

YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter.
But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".

View 14 Replies View Related

Advanced Filter, Criteria Blank Cell

Jan 7, 2010

I cannot work out the syntax to run an advanced filter for all Non-Blank Columns! I have attached an example sheet, and you can see my syntax in cell D4. I am looking to bring through a list of all line that are both Status= "NOT QUOTED YET" and Project Name= NOT Blank. Unfortunatley, the Status Row will be set as NOT QUOTED YET when there is no project name enetered, hence the problem.

View 2 Replies View Related

Advanced Filter For Data Not In Criteria Range

Aug 12, 2006

I m Playing around with AdvancedFilters. Using the code below I can filter for data in the CriteriaRange, but I want to filter for data not in the CriteriaRange. I can't seem to find out to do this. I'm wanting to execute this sub from a button on a userform.

Sub Filter1()
Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("CRng"), Unique:=False
End Sub

View 8 Replies View Related

Advanced Filter Dynamic Rows Of Criteria

Mar 27, 2007

attached is a spreadsheet effectively replicating a criteria box Iím using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What Iím trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.

Below is the code Iíve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, Iím hoping the macro would only use those 2 rows instead of picking up all 7 rows.

See criteria box on spreadsheet for example. In this particular example, Iím wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with ď1Ēs. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?

Sub RunDynamicSelection_Click()
Dim wsSheetDS As Worksheet
Dim wsSheetRS
Set wsSheetDS = Worksheets("DataSheet")
Set wsSheetRS = Worksheets("ReportSelection")
With wsSheetDS
.AutoFilterMode = False
With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _
.UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................

View 2 Replies View Related

Adding Advanced Filter Criteria Without Altering Pivot

Feb 8, 2012

At work, I have a workbook with multiple tabs that contain lists. Each tab has a corresponding Pivot Table.

There is a business requirement that a user can specify a name, which filters the data. For the sake of simplicity use this for an example

Column Headers: First_Name ; Last_Name; Age
Row 1: Johnny; Bravo; 29
Row 2: Shane; Falco; 34
Row 3; Bobby; Shane; 15

The user specifies "Shane" as the filter in another pre-determined Cell (D1). Using advanced criteria, I need to find all rows that have Shane in either First_Name or Last_Name. The only way I know how to do that is inserting two rows and adding criteria:

First_Name ; Last_Name; Age
=D1; ;
;=D1;
Johnny; Bravo; 29
Shane; Falco; 34
Bobby; Shane; 15

With Criteria Range = "A1:B3"

This is problematic because my Pivot tables now include 5 rows of data.

View 3 Replies View Related

Advanced Filter To Extract Records That Meet The Criteria

Nov 2, 2008

I am trying to use an advanced filter to extract records that meet the criteria in the blue input cells. I can't get the criteria correct to allow me to meet the 3 conditions. There are duplicate names in the list so I will need to use unique records only option....

View 9 Replies View Related

Index All Items In Column Based On Multiple Criteria?

May 15, 2014

I am trying to create a list of all instances where contents in A3 is found in C5:C12 and return the values in D5:D12 without any spaces. Right now I can do it in two steps but I'd like to clean it up and do it with only one formula.

View 7 Replies View Related

Forcing Advanced Filter To Cancel Criteria If Found No Matches.

Nov 25, 2009

I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.

If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.

Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.

View 3 Replies View Related

Advanced Filter: Exclude Blank/Empty Cells Criteria

Sep 11, 2006

Im using an advanced filter that uses the following criteria
Days Late Note(s) Note(s)
>90 <>*agreement* <>*QTR*

This shows all data over 90 that do not incl the words agreement or QTR in a column marked Note(s).

My problem is that I do not want to show records that are Null in the Note(s) column.

Note(s)
<> Does not work (possibly because it is text and not Numbers)

as this does work if used on records that contain numbers.

View 9 Replies View Related

Filter Rows Based On Multiple Criteria

Mar 19, 2007

Example attached. I need to filter rows based on a start date and stop date, columns C and D. So for example the filter date is 01Mar07 (located in A5). As this date in this cell is changed the rows are filtered accordingly. I need to filter rows so that any row with a start date which includes Mar 07 is shown and I need to include all rows that have an end date in Mar 07. This would result in the inclusion of an event that started in Feb and Ends in march being displayed.
Additionally, I would need to clear the filter. I'm just starting out, I'm sure this is easy for you all the excel experts., and you may probably have a better method to approach this.

View 2 Replies View Related

Multiple (Not) Lines Of Advanced Filter

Jun 23, 2014

I have a data table consisting of entries; for a simple example,

Column header

Entry A
Entry B
Entry C
Entry D
Entry E

I want to apply an Advanced Filter to Exclude multiple items. Say I want to exclude entries A and B. In my Advanced Filter criteria range I entered:

Column header
A
B

But since Excel reads each line of Advanced Filter criteria as "or" - i.e. does not equal A OR does not equal B - nothing gets filtered. I know the proper way to use AND in Advanced Filter is with multiple columns, such as:

Column Header
Column Header

A
B

But my exclusions will be dynamic so I would prefer to have my criteria listed vertically instead of horizontally.

View 4 Replies View Related

Show (Filter) Rows Based On Multiple Criteria

Jan 29, 2009

I'm trying to create a formula (used in sheet 2 column B) that would generate the results in sheet2.B based on the contents of sheet 1. sheet2.B2 would contain a formula searching for the both "Y" in sheet1.C and the text in sheet2.A2 (in this case "E&P"). When each match is found, the contents of sheet1.A should be returned to sheet2.B, as shown in the mockup. There will be multiple matches (at least 15-20) for each search criteria, and I don't know how many there will be ahead of time.

I've tried various formulas, and they either have incorrect syntax and return every row in a range, or only return the first match correct match each time instead of all correct matches. I found some UDFs posted online that claim to do this, but I've gotten them into the spreadsheet and they generate # NUM errors. I would prefer to work with Excel-native formulas if possible, because I'm going to eventually hand this off to someone else to maintain and they may not have the level of expertise to deal with UDFs.

View 4 Replies View Related

Multiple Filters ,Advanced Filter And Autofilter

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

Advanced Filter To Cells With Multiple Values?

Oct 23, 2012

I'm trying to set up a a spreadsheet where a user can enter search terms to filter a list of data. I'm using Advanced Filter so I can use wildcards and operators (>, < etc) and copying the results to a "results" sheet.

My problem is that some of the data entries have multiple values associated with them and this makes the filters values act funny. I have come up with two ways of setting out the list so far but both have faults:

Option 1: Use Alt+Enter to separate multiple values ie:
Description
Asset #
Site
Product Type
Speed
Comments

[Code] ....

I can filter this table for machines with a speed of 1000 using the advanced filter parameters:

Description
Asset #
Site
Product Type
Speed

1000

*1000

1000*

The user gives an input of "1000" and the spreadsheet automatically adds the wildcard * on the two rows below so that the advanced filter looks for Speed = 1000 OR *1000 OR 1000* (this collects the speeds 15001000 and 1000800) I would get this as a result:

Description
Asset #
Site
Product Type
Speed
Comments

[Code] ....

The Problem: I can't think of a way to use operators like > or

View 9 Replies View Related

VBA To Hide / Show Columns Based On Filter And Multiple Criteria

Mar 8, 2014

I have a very large table and i need to be able to Hide/show specific ranges based on:

Filter +and+ specific cell values in columns

brief example of the table : tablee.png

So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.

There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".

View 1 Replies View Related

Hang Of Advanced Filters With Multiple Criteria

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

Multiple Filters Or Advanced Search Criteria Not Working In Conditional Formatting?

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

Remove Duplicates And Populate Items Based On Dropdown?

Feb 10, 2014

I have an excel sheet with data where I have column A index number, B company name, c empty, d data , e with item.

[Code] ......

What I trying to do is I made a dropdown for company list and i select comp1 then i want to populate items list with out duplicates .

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved