Formula To Delete Column If It Doesn't Match Criteria

Jun 2, 2009

I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).

View 4 Replies


ADVERTISEMENT

Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

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

Delete Any Rows In Which Column B Doesn't Have ...

Oct 27, 2008

I'd like to tell Excel that anytime it finds the text "ABC" anywhere in any cell in column B but doesn't have any data in column A, to delete the entire row.

View 6 Replies View Related

Highlight A Cell Formula Result If It Doesn't Match

Sep 28, 2007

I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum.

This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.

View 9 Replies View Related

Delete Rows That Match Criteria

Feb 2, 2009

I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.

View 5 Replies View Related

Delete Rows That Dont Match Criteria

Jun 19, 2007

I have a file which has some data I want to keep and some data I want to strip out.

The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.

Example file shown below

23-Apr-0812341234123412341234
30-Apr-0812341234123412341234
19-May-0812341234123412341234
27-May-0812341234123412341234
> 1 year12341234123412341234
Overdue12341234123412341234

View 9 Replies View Related

Formula Doesn't Insert Or Copy Down Column?

Apr 14, 2014

I have this formula that I'm trying to insert into cell AE2, then copy down the entire column. When I execute it, none of the cells are populated.

Range("AE2").Select
ActiveCell.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$2/365)/P2)"
Range("AE2").Select
Selection.AutoFill Destination:=Range("AE2:AE3495")
Range("AE2:AE3495").Select

View 5 Replies View Related

Delete Multiple Columns By Column Name No Criteria Need, Just Delete Them

Jun 24, 2009

I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.

And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth

View 4 Replies View Related

Nested Index / Match Array - Return Value In Column C When Matching Column A And B But With Few More Criteria

Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A
B
C

1
Cat 1
January 1, 2014
John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies View Related

Find And Match The Data In Column In A With Column B And Delete Both

Mar 28, 2014

I need to find the data in the sheet SKU & match it to the data in import_215 column F if it matches then delete the entire row (see Attachment SKU3)

View 12 Replies View Related

How To Delete A Row Based On Partial Match In Column

Jul 19, 2013

I am using VBA

I have an input box pop up to ask the user for a base item number. The way the excel sheets part numbers work for example is TM-T88VP-GRY, the last part 'GRY' changing based on the variation of the item that someone ordered. So, the base item number would be TM-T88VP, and what I want to do is search through a set column (Column G) and delete any row that contains that base item number.

View 4 Replies View Related

Using Index And Match For Two Criteria - One In A Column Other In A Row

Dec 20, 2012

How to get the following index/match formula to work.

I have 2 criteria that I need to find the result of in a table that has the one criteria down the column, the other across a row at the top.

The formula I have is thus far:

{=INDEX($K$46:$AV$46,MATCH(L98,IF($K$15:$AV$15=N98,$K$27:$K$82),0))}

In this L98 is the criteria I'm in theory doing a vlookup against the values in column $K$27:$K$82, and N98 is the theoretical hlookup against row $K$15:$AV$15, bringing back the intersection point from row $K$46:$AV$46

All I get is #N/A .....

View 5 Replies View Related

Macro To Match Column Against Row Criteria

Feb 10, 2014

Trying to write a macro that will display either an 'X' or a date under a specific header if several conditions are met across a row in another tab.

I have two tabs, one is the report, the other raw data. On the raw data tab, if the macro finds a row where the title, territory, Rowdesc, start and end dates all match criteria, then display an 'X' next to that title in the report tab. The criteria would be:

Title Match - between Raw Data tab and Report tab
Terr_Nm column matches report header in Report Tab
Rowdesc = 'No Rights'
Start Date = the date 1-Jan-1900 or 1
End Date = * - an asterick

However, if the macro finds a start date that is greater than today, display this start date on the report where the 'X' would normally go. If the macro finds a start date that is less than today and an end date that is greater than today, then display the end date where the 'X' would normally go.

If the macro does not find anything, then do nothing and go on to the next row. In my example, 'Title 4' isn't on the raw data, so the macro would do nothing with this title.

Raw Data:
Title
Terr_NM
Rowdesc
Start
End

TITLE1
Terr X
No Rights
1/1/1900
*

[Code] ....

Report Tab, before running macro:

TITLE
Terr X
Terr Y
Terr A
Terr B

TITLE1

TITLE2

TITLE3

TITLE4

Report Tab, after running macro:

TITLE
Terr X
Terr Y
Terr A
Terr B

TITLE1
X
1/1/2015
X

[Code] ......

I've tried doing this with formulas (dcount, countif, etc.) and the problem is displaying the specific date. So, I'd like to use VBA to automate.

View 1 Replies View Related

Coloum Match: Doesn't Match Then Result Should Give "Please Check Again".

Dec 15, 2009

i have weights entered in A1 , B1 , C1 and I manually enter almost same weight in D1, E1, F1 . Now I need the result as " OK " in G1 if the weights A1,B1,C1 match with D1,E1,F1 if it doesn't match then result should give "Please check again".

View 9 Replies View Related

Formula To Sum Multiple Match Criteria?

Jan 28, 2014

I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.

There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).

Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.

Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.

View 1 Replies View Related

Copy Formula Down If Criteria Match

Sep 18, 2006

I need to copy down formulas from Row 4 to all rows below til end if value Col F > 0.

All other data on subsequent rows must remain intact.
Need to loop through all shts in the wb

Guess where I'm stuck, I'm not sure how to setup the destination range for the paste

Here's where I'm at so far
Just a snippet, not complete:

Sub Evaluate_Paste_Formulas()

Dim wbBook As Workbook
Dim sht As Worksheet
Dim rngCopy As Range
Dim rngDestination
Dim lngRows As Long

View 5 Replies View Related

Show Info In Column C, If A & B Match Criteria

Aug 20, 2009

What I'm having a hard time is trying to get information from column C to pull into a cell, for a specific student (column A), but only when the entry happens between two set of dates (column B).

Column A = Student Name
Column B = Entry Date
Column C = Entry

So in a different worksheet, the names of the students will already be on the report and each column following will have a date range of a week. So for column B I want the entry that only happened between "09/07/09-"09/13/09". No student will have more than one entry a week so I just want it to locate the entry and fill in automatically. This will repeat for each week after as well.

View 3 Replies View Related

Index Match Formula For Multiple Criteria?

Dec 3, 2013

In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.

In cell B12 i have an index match that I can not get to return a value.

Is Index Match the correct method of returning a result? Or have I just got the formula wrong?

View 1 Replies View Related

Multi Criteria With MATCH Or LOOKUP Formula?

Mar 20, 2014

What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..

and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.

and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..

View 3 Replies View Related

Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?

View 1 Replies View Related

Index & Match Formula: Multiple Row Criteria

Jun 20, 2007

I am trying to get my INDEX & MATCH formula to retreive data from my table.

This is what I can do so far:
Jan-07Feb-07
100 12
250 45

=INDEX(table,MATCH(B13,balance),MATCH(C13, date))

But I am trying to get it to get another row to look up as well.
I want it to look up the color then the 100 or 250, then the date.
Jan-07Feb-07
red100 12
red250 45
blue100 78
blue250 1011

=INDEX(table,MATCH(B13,balance),MATCH(C13,date))
I think i need to insert another match code in the row section but cant seem to get it to work.

View 7 Replies View Related

Formula - Average Values That Match Criteria

Jan 3, 2008

The attached workbook has two tabs:
1. Burn Rate - this is where I need my formula to calculate
2. prorder - this is where the table will be

What I need:

1) from 'Burn Rate', get the 'PO ID' we will look up in the table.
2) go to 'prorder' - when the 'PO ID' there matches that same 'PO ID' from the other worksheet - take the value in column F - this is the number that will be averaged.

In other words - in 'Burn Rate', for a given 'PO ID' (column A), I want to average all of the values that are found in column F in 'prorder', and return that average to 'Burn Rate' (column B).

View 4 Replies View Related

Match 2 Criteria And Search Column And Return Value Based On Value

Feb 14, 2014

I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:

If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.

2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE

View 3 Replies View Related

Match Two Columns Across Sheets And Based On Criteria Of Another Column

Aug 10, 2009

This is driving me nuts. What I have is:

Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text

Sheet 2

ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values

What I want is in Column F (Sheet1) a formula to:

if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True

View 9 Replies View Related

Delete Rows If Column Has Fewer Than X Matches To Part String Match

May 5, 2008

I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.

Data set example:

In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.

Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
.AutoFilter

I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.

View 6 Replies View Related

Copy Rows Where Cells In Column Match Condition/Criteria

May 5, 2008

I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet

This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either.

I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".

Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate

View 9 Replies View Related

Delete Certain Cells Within Row Or Column Based On Criteria?

Nov 7, 2013

What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?

Also i need this to work on all atbs as the commandbutton will on a master tab

VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False

[Code]....

View 4 Replies View Related

How To Delete Entire Row Based On Criteria On A Column

Oct 19, 2011

Simple code that can delete entire row if certain criteria is met in a single cell

Example

I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"

How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.

View 3 Replies View Related

Selecting Column And Setting Criteria To Delete

Feb 15, 2012

I'm trying to delete a column based on a simple criteria. If the column contains no data it has to be deleted, but I really couldn't do it.

Code:
With Sheets("Test")
Columns("AH:AH").Select
If Columns("AH:AH") = "" Then
Selection.Delete Shift:=xlToLeft
Else
Exit Sub
End If

View 6 Replies View Related

Delete Rows Meeting Criteria In One Column

Jun 4, 2009

I have a large number of user inputs which is linked to a data sheet. In this sheet are rows to be kept and rows to be deleted. See attached example...

I need code to delete all other similar rows than the one with a number (1) in the field Complete. There can only be a number (1) in the field Complete in one of the rows.

But if there is no number (1) in the field Complete in any of the rows, no rows should be deleted.

The data sheet is to be transferred to Access afterwards.

View 3 Replies View Related







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