I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.
View 3 RepliesIn sheet1 I have the following
1. Names (Column A)
2. Date (Column B)
My current list if from A2:B300
I am trying to have a piece of code go through all the records in this list, if any of the dates are within the next 2 weeks (from now()) then copy this record (name, date) and put in sheet2. And loop through all 300 records or possibly additional records.
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
i have a large set of data, with a data in column a & column e & some text in column F
i need macro to remove rows where the date in col a is earlier than 6 months before the date in column e & column f has the text "a/g or "g"
I have a table that extends from A1 to S630 (the number of rows changes as records are added and deleted).
The function to add rows based on the date is fine and I have implemented, but I cannot figure out how to delete a row based on a date.
The date is entered on a sheet called Calendar in Cell J20
so Sheets("Calendar").Range("J20")
and the date is in column B of the table, which has been made using the Table function
Is there a way of scanning through the whole table deleting all the rows with the date entered into J20 on the calendar sheet.
I am new to to VB Scripting, filter the records. In the attached file there are multiple records which needs to be filtered. Once all the records are filtered, i want to delete the entire row of that record(s). I want to filter column 'F' with the values mentioned in Sheet2. I tried recording a macro, but it is not allowing me to do so as the macro has some limitations. The search and delete row loop The main purpose is to delete the row of the value, if not found, move on to next value in Sheet2.
View 4 Replies View RelatedI have a large (300K+ records) database with a sizeable amount of duplicate records. I want to delete the duplicates but this is not a matter of simply Remove Duplicates; I need to evaluate them before I do.
I am wondering what functions would:
1) select the specific record in a set of duplicates that makes a determination of a status
2) once the status has been determined for the set, delete all other records
Fields in my database:
ACCIDENT NUM (ID field, in text or General format)
DUP (for Duplicate, indicated by a character, for now its a "?")
OCC_KILLED (in Number format)
OCC_INJURED (in Number format)
SEVERITY (in text format)
Here are some scenarios:
ACC dup K I
12345 ? 0 0
12345 ? 1 2
Or:
ACC dup K I
123456 ? 0 1
123456 ? 1 0
Or:
ACC dup K I
1234567 ? 0 0
1234567 ? 0 2
1234567 ? 0 0
This is the formula for indicating if there are Duplicate records in the larger dataset:
=IF(OR(A2=A3,A2=A1),"?","")
I need to determine the Severity of the accident based on this:
If OCC_KILLED > 0 then SEVERITY = F (for Fatal)
IF OCC_INJURED > 0 and > OCC_KILLED then SEVERITY = I (for Injury)
IF OCC_KILLED and OCC_INJURED >= 0 then SEVERITY = F
IF OCC_KILLED and OCC_INJURED = 0 then SEVERITY = PDO (for Property Damage Only)
I have a code already in place for how to create the value for Severity but it DOES NOT account for duplicate records:
(in SEVERITY field):
=IF(A1<>0, "F",IF(B1<>0,"I","PDO")
sampleset.xls is a sample ot the database.
I have a spreadsheet with a column of records (text ) from B1:B4000. I would like to delete the entire row of the duplicate record. Overall data set range is (A1:I4000). This spreadsheet may contain more than 1 duplicate record. Is there in easy fix?
View 12 Replies View RelatedI'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.
Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No
E4 No
Expected results:
E1 Yes
E2 Yes
E3 Yes
E4 Yes
I have some data that looks like this.
No. Date
1519
1519 06/17/2003
1601
1652 06/30/2005
1652
1705
1705
What I would like to do is delete all numbers including those with duplicates that have a date of 2004 or sooner. In otherwords, anything from 12/31/2003 and later I want to keep, but anything from 01/01/2004 until the present.
I'm creating an Excel UserForm where the user can view, edit, and delete records they've entered.
The following code is for Deleting a selected record, and it IS WORKING. But it seems TOO SIMPLE and I want to be sure it's correct and not leaving any loose ends in the Database file that could cause corruption later on....
Sub vCLdbDel()
Dim cnt As ADODB.Connection
Dim dbPath, dbName As String
Dim stSQLAs String
Dim stCon As String
Dim dbid As Long
Run "setvars"
'Get the dbID from the selected Item in the list
With vCL.CLdbList
dbid = .List(.ListIndex, 6)
End With
'Path & FileName to the Database File
dbPath = M. Range("G2").Value
dbName = M.Range("G3").Value
I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.
View 3 Replies View RelatedI need to have a User input a date via an InputBox, and then based upon that date, delete rows based on values in Column G. For instance, if a user inputs 12/25/2006, I need to delete all rows that have the date that is less than 12/25/2006 in Column G. There will never be more than 500 Rows of data, and there is data in Columns A thru Z
View 4 Replies View Relatedmodifying the following from deleting rows older than 365 days to delete rows older than 12 months. So I can keep a rolling year by month.
Sub DeleteOldSR()
Dim x As Long
Dim iCol As Integer
Application. ScreenUpdating = False
iCol = 7 'Filter on column G (Create Date)
For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
s = Cells(x, 3).Value
If s Like "Closed" Or s Like "Closed w/o Customer Confirm" Then
If Cells(x, iCol).Value < (Date - 365) Then
Cells(x, iCol).EntireRow.Delete
End If
End If
Next
Application.ScreenUpdating = True
End Sub
I tried modifying the IF statement using month with the following but it seems to delete all months < and is not year dependant.
If Month(Cells(x, iCol).Value) < Month(Date) - 12 Then
I d like to find a method with which I can delete records from E starting from E2 that have 0 or even blanks with a macro.
View 9 Replies View Relatedhow to automatically complete with Excel the following task:
1. I have two email databases in Excel: a master database and opt-out database.
2. I need to remove from the Master database emails of opted-out people.
3. Since the databases are large, I'd like to use a relevant Excel function to do that automatically.:
In the master list (column B) I have all the emails from the Master list. I have copied in the column C of the Master list the emails of all who opted-out. I need to remove opted out emails (listed in column C) from the master email list (column B).
I d like to find a method with which I can delete records from E starting from E2 that have 0 or even blanks with a macro.
View 9 Replies View RelatedI need cell A1 data deleted once the date in B1 has passed.
View 7 Replies View Related1. Is there a way to delete all records below a variable row which I select highlighted ?
2. I have a spread sheet which is located in the folder xl start. How can i find this file to delete it>
I'd like to do is click the delete button and when clicked, it will search for matching records in column A & B and if they match... I'm thinking the code for that is <> but I'm not sure, then delete that record, and shift the cells up. Do this until the search results are empty below the delete button. Like I said, it's probably more understandable to look at the workbook.
View 5 Replies View RelatedCreating a macro. Need to delete records in column D from the first blank cell. 1st blank cell variable.
View 9 Replies View RelatedI have a table in excel which looks something like this:
Product Ref
Date
Description
1
29/05/2013 19:58:50
Product A
[Code] ....
All I'm trying to do is get the only the first dates for each of the related Productsrefs. So the result should look like this:
Product Ref
Date
Description
1
28/05/2013 19:26:48
Product A
[Code] ....
Is there a built in function in excel that can achieve this?
I need to get dates from one file to another. They can be matched on ID.
So if the ID for a record from FileA matches the ID for a record in FileB, return the date from the date from the same row in FileA to FileB. I would just sort and copy them over, but FileA has more records than FileB. All distinct IDs share the same date.
We have multiple work orders per opportunity, and each work order has a date. I have a sheet of these work orders which shows the work order #, work order date, and opportunity date. What I want to do is add a column which reports the work order date if it is the earliest instance of a work order for the opportunity (said differently, the first work order for the opportunity). Is there a way to write a formula to report whether the date of the row is the earliest date for a given opportunity, all in the same table?
View 1 Replies View RelatedI have a table in a sheet and I want a formula that will count the unique records. The simplified database is as follows (in a table):
View 6 Replies View Relatedi have to removed the duplciate records with older dates. The date column is made up of date and time.
IDExpiry Date
1202 Mar 2013 12:57:04
1220 Apr 2013 10:49:24
5020 Apr 2013 10:57:06
5021 Oct 2013 07:20:54
5024 Oct 2013 08:41:56
I have a master list that is updated in real time by linked worksheets from several people. While my actual report covers 7 managers and 7 different products (“contract types” in this example), I have summarized and simplified the input in the attached file. There are three pertinent dates – the date assigned to the manager (col A), the date completed by the manager (col F), and the date approved by the executive (Col G).
These dates will span month ends and I need to be able to provide monthly reports that provide details on the number of contracts and the dollar value all contracts assigned and completed by the manager during the month (say, February) as well as any contracts assigned in any month which remain uncompleted at the current month-end (e.g. the $7,500 Smith contract assigned on Feb 21st that remains uncompleted and the Feb 26th $200 Jones contract which was uncompleted at Feb 28th, but completed in early March, in this example). Can this be done directly in a Pivot table from the full master list, or will I need to have separate sheets in my workbook where I extract the pertinent records for that month?
I have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:
Below is how data in master sheet1 looks like...
STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...
With the formula or a macro I want it to look like this in Sheet2 in the same file...
STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .
As you can see there are 5 records per STORE# listed in Master Sheet1.
I have an excel file that gets records from access database. I have a field named "Class" in column C which starts at row 4. I want to filter the records in such a way that only those records with Class equal to whatever value I put in cell B2 will be shown.
For example:
A B C
REPORT
Filter Class: _______
ProjID Name Class
001 Project A 4
002 Project B 4
003 Project C 4
004 Project D 8............