Delete Cells Based On Multiple Conditions?

Jul 16, 2013

I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:

Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i

but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.

View 4 Replies


ADVERTISEMENT

Identifying Rows To Delete Based On Multiple Conditions?

Jun 5, 2014

Maybe this has been taken care of in another thread, but still:

ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1

I have a situation like above, but what I want to do is:

=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,

(create name for set?)

AND

IF OCC_K AND OR OCC_I VALUES >= 0,

THEN DELETE ROWS(within set) >=2

Or more simply:

If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?

Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.

Is it also possible to use AND and OR in the same line? Or BOTH?

(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)

I would like to think I'm not going to need a macro, and that I can do this in a Formula.

View 10 Replies View Related

Color Cells Based On Multiple Conditions Of Different Cells

Jan 15, 2008

Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View 6 Replies View Related

Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

View 8 Replies View Related

Multiple Conditional Format Conditions Changed Based On Another Cells Value?

Jun 12, 2009

A1:A6 have numeric values 1 to 6.

I want the conditional format values to change Based on the value in Cell B2.

If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.

IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.

I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.

View 6 Replies View Related

Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

View 10 Replies View Related

Delete Rows, Multiple Conditions

Jul 20, 2009

I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:

1Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet

Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions.

View 9 Replies View Related

Delete Multiple Rows Under Certain Conditions

Aug 1, 2008

I need to eliminate the passing jobs, leaving the failing ones so I can do coaching sessions with the guys that are missing the mark.

Conventions of the report:

Each job has a 20 digit job # and is allocated 12 rows worth of information fields unless customer has more than 1 cable outlet with a modem, then the rows increase by 4 for each additional modem
Tech name is repeated after every 12 rows even if within the same job #
On passing jobs, column I will only contain Yes (there are blank cells in column I, as not every parameter is checked on every job), failing jobs will contain No in column I

Items I need to accomplish:

Increase column C width to 22 and column E to 25

Format column C to number, 0 decimal places (to avoid scientific notation)

Move tech name to beginning of each job # and eliminate repeated names

Delete all rows plus tech name on passing jobs

Change cell color to red in rows (columns A-I) that contain No in column I

I have highlighted each different job # in different color


John Smith10002934422610200000Outlet 2D - Up Stream SNR274029.4Yes10002934422610200000Outlet 2D - Up Stream Tx365546Yes10002934422610200000Outlet 2D - Down Stream .............................

View 10 Replies View Related

Delete Rows Based On Two Conditions

Mar 29, 2012

I need a macro that loops through all rows (With the exception of the first row) and looks for two conditions. If those two conditions are met, I'd like for it to delete the row.

The first condition is if Column O does not contain a certain phrase "VTX" VTX isn't the complete value of the column, it might just be contained within a few other notes in that column.

The second condition is if Col S equals "COMPLETE/FOLLOW-UP IMAGING".

So if these two conditions are met, Col O does not contain the word "VTX" and Col S equals "COMPLETE/FOLLOW-UP IMAGING", then that row should be deleted.

View 9 Replies View Related

Delete Entire Row If Meets Multiple Conditions

Aug 26, 2013

I am trying to create a macro to accomplish the following task. Delete if the value belongs to queue "ab cd ef" and is also a part of category "xxx xxx".

I wrote the following code, but it seems it is doing some mistake:

Code:
LastRow = Range("Z2").End(xlDown).Row
For i = 2 To LastRow
tempQueue = Range("C" & i).Value
tempRange = Range("Z" & i).Value
If (tempQueue = "ab cd ef" And tempRange = "0 - 1 day") Then
Range("C" & i).EntireRow.Delete

[Code] ........

The above code works fine but it deletes only 120 rows out of 200 or sometimes 150 out of 200. Can't get it to work all 200 rows.

View 4 Replies View Related

Vba To Delete Rows Based On Certain Conditions In Column A

Mar 2, 2006

I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).

View 9 Replies View Related

Sum Based On Multiple Conditions

Aug 28, 2009

I am trying to sum numbers based on three counditions.

My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

View 4 Replies View Related

VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

View 9 Replies View Related

Summing Based On Multiple Conditions

Feb 11, 2010

I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:

1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6

I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.

View 14 Replies View Related

Cell Value Based On Multiple Conditions

Feb 23, 2010

I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

View 7 Replies View Related

Average Based On Multiple Conditions

Dec 1, 2009

Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:-

=IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10))

This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0))

This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.

View 4 Replies View Related

Flagging Duplicates Based On Multiple Conditions

Nov 8, 2008

Flagging Duplicates based on Multiple conditions..

I have attached the file for reference with Dummy Data as the actual data runs in thousands...

What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records...

The Duplications has to be considered on Multiple criterions:
1. If the FNAme and LName is appearing for one Company more than once.

2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same.

3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate".
Ex: -

4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique.
Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique

5. If still the records are identical then the first occurence can be unique...

I have made this data to give an example as the actual data is very confidential.

Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic.

Please refere the Word doc Duplication Criterion Explanation for the explanation.

View 14 Replies View Related

Lookup Unique Based On Multiple Conditions ...

Jan 2, 2009

I am looking for a solution other than using an advanced data filter for unique records only.

I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).

Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.

Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?

SEE ATTACHED:

View 9 Replies View Related

Counts Info Based On Multiple Conditions

Feb 22, 2009

I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point

I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following

What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute


column A = Home Team
B=Away Team
C= Time of First goal
D=Time of Second goal
E=Time of Third etc

I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50.

My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1.

The sheet has a lot more columns but this is the main sticking point.

View 10 Replies View Related

Finding Information Based Upon Multiple Conditions

Mar 23, 2009

I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:

Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift,
Week 2, Bill is working the morning shift and Aaron is working the afternoon shift
Week 3, Aaron is working the morning shift and John is working the afternoon shift
etc etc

I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.

View 5 Replies View Related

Match Function Based On Multiple Conditions In VBA

Jun 7, 2013

I am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.

My code is like this.

Dim Col_num As Integer
Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1
Columns(Col_num).Select
Selection.EntireColumn.Insert
Cells(6, Col_num).Select

This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.

View 9 Replies View Related

Lookup Value From List Based On Multiple Conditions

Dec 21, 2007

I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.

View 6 Replies View Related

Create New List Based On Multiple Conditions

Mar 6, 2008

I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:

IBM US 3 C60 Equity
IBM US 3 P60 Equity
UKX 3 P5000 Index
UKX 3 C5000 Index

I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.

----CODE----........................

View 8 Replies View Related

Sum Based On Multiple Conditions & Variable Range

May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

View 2 Replies View Related

Return Data From Table Based On Multiple Conditions

Jul 26, 2014

Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?

View 4 Replies View Related

Record Duplicate Values Based On Multiple Conditions Only

May 1, 2014

I am trying to pick out certain bits of information from the below "example" set of data:

A
1

A
1

B
1

C
1

[Code] .......

My aim is to record the letters that are recorded against both numbers (note: in my data there are more than 2 sets of numbers). For the example above the solution would be:

A
1,2

B
1,2

...because these two letters appear against both 1 and 2.

There are some letters that are duplicated against the same number which is making it hard for me to work out. I don't care if the same letter appears against the same number, I just would like to know instances when a letter appears with a different number, and if possible what that number is.

View 3 Replies View Related

Calculating The Average Of A Column Based On Multiple Conditions

Feb 21, 2008

How do I calculate the average based on multiple conditions? Is my syntax incorrect?

Here's what i'm looking for:

=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))

View 9 Replies View Related

Generate Numbers From Another Sheet Based Multiple Conditions

Nov 21, 2007

I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.

View 9 Replies View Related

Highlight Cells With Multiple Conditions

Jun 27, 2014

So I need highlighting certain cells. I have a sheet that keeps track of people's points over a series of races to determine who will make the "team". The team is selected one at a time based on the most points, starting with the leftmost column and working right. What I would like to do is highlight the highest score in each column (which I know how to do) but then tell the sheet to ignore all the other numbers in that same row. That way if the person with the most points in column one (M in my sheet) also has the most in column two (N in my sheet) Excel will ignore that number in the second column and highlight the next highest number.

I'll attach my file so there is a visual reference : ODN Points.xlsx‎

View 3 Replies View Related

Count Cells Using Multiple Conditions

Jul 1, 2008

I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.

I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes

View 4 Replies View Related







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