Delete Rows Based On Data From Two Columns?

Sep 21, 2012

two things on the attached sheet.

First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?

Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!

View 6 Replies


ADVERTISEMENT

Delete Rows Based On Data In Two Columns

Mar 18, 2014

I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.

View 1 Replies View Related

Remove Empty Rows Based On Range Of Columns If Columns Are All Empty (no Data) Delete

Oct 24, 2012

Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.

VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range

[Code].....

View 1 Replies View Related

Delete Rows Based On Two Columns

Dec 22, 2006

I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.

In Column L it has either numbers 1 through 100, or the letter "D".

What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.

Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".

It might be easier to do it separately because once you run the part that deletes the rows that dont have a C or LP then I could probably just do one that says if column L = D then change to 1.

Lets see if I can simplify this for you.

If Column L = D and Column A = BLANK then Delete Row
(this would delete all Ds that dont have the C or LP)

THEN

If Column L = D then change to 1

View 9 Replies View Related

Delete Duplicate Rows Based Only 2 Columns.

Feb 18, 2010

I need to filter the following file.

I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.

The end result should look similar to columns F,G and H!

View 2 Replies View Related

Delete Duplicate Rows Based On All Columns

Jan 7, 2010

How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.

View 9 Replies View Related

Delete Rows Based On Duplicates In X Columns

May 14, 2006

I've been looking through various forums looking for a macro for a particular task, but I have not found something that works yet. Maybe someone could help me out? It's pretty straightforward situation.

I need a macro that removes an entire row when: information in column B and column C are both found in other rows ( duplicates).

I'm dealing with a worksheet with store record information ... Store Name (column B), Street Name (column C), State/Province, etc. I import new store records all the time and many are invalid because they share the same name and street address and therefore need to be removed. The ones that Ive played with unfortunately end up deleting the cells only and not the entire row.

It's a tedious process to do manually remove entire rows (for duplicate column B&C) when dealing with thousands of rows and I would very much appreciate any assistance or insight into how simplify the process with a macro. I've attached a sample of what I'm working with.

View 9 Replies View Related

Delete Duplicates Rows Based On 2 Columns

Dec 6, 2006

I want to delete the current row if the data on row A and Row B match. Its hard to explain so I posted an example.

View 5 Replies View Related

Moving Data From Rows To Columns And Delete Repeated Rows

Apr 16, 2014

removing duplicate rows and move other data frm rows to columns.xlsx.

I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.

see the desired result tab in the sheet to get an idea of what I am looking for as the end result.

Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.

View 5 Replies View Related

Delete Columns And Rows Based On Cell Fill Color

Jan 31, 2014

I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.

Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.

View 2 Replies View Related

Find The Last Row Without Data In Columns E And F And Delete The 10 Rows Below Them

Mar 14, 2008

I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?

View 3 Replies View Related

Concat Rows Based On Duplicate Data & Delete

Jun 18, 2009

I need to concatenate and de-dupe. I want to find dupes, concatenate a column, and the delete the duplicate columns--all while maintaining data from other populated columns.

Here is the some example data:

#| Type | Name
1 | A | Bob
1 | B | Bob
1 | C | Bob
2 | B | Sally
2 | E | Sally
3 | A | Bill
3 | C | Bill
4 | E | Nancy

The result should look like:

# | Type | Name
1 | A, B, C | Bob
2 | B, E | Sally
3 | A, C | Bill
4 | E | Nancy

I am using Excel 2003.

View 5 Replies View Related

Delete Rows Based On Seperate Column Data

Nov 29, 2006

How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.

View 4 Replies View Related

Deleting Rows Based On Columns Data

Jul 2, 2007

I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...

View 9 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )

View 3 Replies View Related

Delete The Columns First Then Rows Will Not Delete

May 22, 2008

Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.

View 12 Replies View Related

Delete Rows With Columns Not Containing (MC)

Mar 29, 2014

I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):

Y
Z
AA

IP/DA/SV

MC/SWL/SA

MC/SH/SA

[code]...

Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.

Below is the code I have been using:

Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Seems simple enough but this newbie chump is stumped why it is not reading the other columns

View 4 Replies View Related

Delete Columns, Not Rows

Jan 12, 2007

I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:

Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.

ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub

The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.

View 8 Replies View Related

Delete Rows, But Not Across All Columns

Mar 4, 2008

Is there a way to delete x # of rows in columns b, c, d, g, j, m, p & s.

I regularly enter data in these rows for comparisons and then need to delete the data with out deleting the formulas in e, h, k, n, q & t.

A macro or one key then delete would be great.

View 4 Replies View Related

Delete All Rows That Have Blanks In Both Columns?

Aug 23, 2012

I am trying to delete all the rows that have blanks in both columns "D" and "F".

VB:
With Sheets("2012")
For k = 6 To 1000
If (.Range("D:" & k) = "" & .Range("F:" & k) = "") Then

[Code]....

View 4 Replies View Related

Delete Rows/columns Across All Worksheets

Jun 24, 2009

I have a single workbook with 55 worksheets. I need to delete rows #1-14, then delete columns A & B on all worksheets. The macro below seems to be running in a loop which appears to delete rows 1-14 and columns A & B until there is no more data left on the active sheet only. It does not run on any of the other sheets - even if I select all sheets before running the macro.

View 4 Replies View Related

Delete Rows With Strings In Only One Of Two Columns

May 10, 2012

I have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this

textCOL1 textCOL2
text1COL1
text2COL1 text2COL2
text3COL1 text3COL2
text4COL1
text5COL1
text6COL1 text6COL2

How do I remove the rows which have text only in the first column?

The output should be:

textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2

View 4 Replies View Related

Delete Rows By Criteria In 4 Columns

Jul 14, 2004

I have a large spreadsheet that is 65000 rows and colomns A thru W.
I need a macro to delete rows that if Date and Account and Type are
equal and the net of Quantity is zero delete those rows.

Col A = Date
Col C = Account
Col D = Type
Col J = Quantity

What the macro would do is if an account had 250 rows of activity on
say 7/7/2004 (Date) and same type of account and the net of all
Quantity is equal to zero delete those rows.

View 9 Replies View Related

Delete Empty Rows & Columns

Nov 17, 2006

I would like to delete all unwanted rows and colomns. I know this can be done but Old Age is catching the grey matter

I want to delete all except the following ranges, Range("A1:H27").Select

View 4 Replies View Related

Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub

View 9 Replies View Related

Delete Rows Based On Values In Rows Below

Jun 20, 2008

I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.

View 2 Replies View Related

How To Delete Multiple Rows But That Match In Columns

May 22, 2014

ertret.jpg

I am trying to delete rows that contain the same addresses BASED ON COLUMNS.

Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.

I want to keep the ones that DO NOT MATCH.

P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH

View 1 Replies View Related

Mass Delete Certain Rows / Columns Each Time?

Sep 19, 2013

Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?

View 10 Replies View Related

Delete Blank Columns And Rows In A Different Sheet?

May 27, 2014

I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.

View 2 Replies View Related

Delete Columns Depending On Number Of Rows

Jun 5, 2014

I have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.

Please see the attached file : Summary.xlsx‎

View 3 Replies View Related







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