Not sure where to start with code to do the following.
I have a Large workbook 150 worksheets in which i need to remove rows based on date criteria.
I need to remove all rows which contain 2011 in cloumn D within the range of B10:T40 without filtering the work sheet first. on every worksheet in the workbook. also I would need the code to skip/ignore sheets that do not have a 2011 date incolumn D.
Time is not a issue for this procedure.
I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.
Columns: Name, Size, Date
nmlclex06114/12/2013
nmlclap121012/12/2013
nmlclex0651/10/2013
nmlclex0619/10/2013
nmlclex0611/01/2014
nmlclap12124/10/2013
nmlclex06113/11/2013
nmlclap12118/12/2013
nmlclap1219/12/2013
nmlclap12130/12/2013
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
I wrote a tool that people at work use. They initially need to paste in a bunch of customer locations with Address, City, State, Zip, etc. Sometimes the Users have "bad input" data that has non-breaking spaces, multiple space between words, or leading and trailing spaces and nonbreaking spaces. I have code to get rid of all of those problems. However, the Users often use their data for other important functions at work. So I want to give them a message to let them know that their Original Data is "bad".
So instead of just "Fix" . . . I want to "Report the problem", then "Fix". I need to identify exactly what problem was found - not just tell the User that their data is bad.
I wrote a simple Search routine with error handling that identifies 2 of the 4 cases and notifies the User:
Case 1) ASCII 160 (non-breaking space, HTML  
Case 2) multiple spaces (2 or more consecutive spaces)
Case 3) Leading or Trailing Spaces (ASCII 032)
Case 4 Leading or Trailing non-breaking spaces (ASCII 160, which is HTML  )
I cannot quite figure out how to find the 3rd and 4th Cases. If anyone can help me with Case 4 especially, then I can probably do the same thing for Case 3.
I think it will work to somehow use this idea - the code is not even real code but it is just conceptual:
RIGHT(CellReference, 1) = Char(160) or Char(032)
LEFT(CellReference, 1) = Char(160) or Char(032)
Anyway, here is what I have so far . . .
Sub NotifyBadInput
ErrorFlag = False
Cells.Select ' select entire worksheet
' BAD INPUT 1 - lLook for any occurence of ASCII 160 (non-breaking space, HTML  )
' and Notify the User if any of his Input cells contain  's
On Error Goto errormsg1
Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
In my current sheet, I have a button which pastes the contents of the user's clipboard into cell A20. The data that is being pasted is a simple, single-column range of data that may or my not contain blank cells. When this button is clicked, the user's clipboard should already have data.
Some of these pasted ranges may go from A20:A40, A20:A60, or even A20:A73. The point is, the length of the pasted information is variable.
I am looking for VBA code which can look at the recently pasted range in A20:AX, find blank cells, and remove them. Here is my current code (very simple), which is only the paste function:
VB:
Sub admin_btnPASTE()
' paste_align Macro
' pastes data
On Error Goto Whoa
[Code]....
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies View RelatedI have several hundred rows of date/times which I need to modify just the date (for example 08/23/2007 11:00 to 08/15/2008 11:00). Each row may have a different time so it is just the date I'm targeting for change.
If I manually use the replace dialog, replacing 08/23/2007 with 08/15/2008 works just fine. However, if in code I attempt to use the Cells.Replace function, it does not locate any data to change.
I have found that if I search for the string 8/23/2007, the dates are located and changed. Only when I attempt to find the fully formatted date 08/23/2007 does the function fail.
I need to create a table of dates which is structured as such:
A B
1/7/08 Yes
7/7/08 no
25/7/08 yes
Column A is date, Column B is a binary selection (yes, no)
I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.
for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.
The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.
This code below is pasted from Automatically Add Date, Time or Date & Time and uses the Worksheet Change Event
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
It automatically enters today's date into the corresponding B cell. How do I code it so that the date is automatically removed when & if I delete the corresponding A cell?
remove duplicate.PNG
is there a way to remove the duplicates in column ICAP ID... but only remove the icap id that has the earliest completed date(completion_dt).
I attached an example excell...
I am trying to remove the duplicate entry of the date the list goes like this
01/22/2014 6:10
01/22/2014 6:12
01/22/2014 6:13
01/22/2014 6:15
01/22/2014 6:17
01/23/2014 7:00
01/23/2014 7:01
01/23/2014 7:02
01/23/2014 7:03
But I want
01/22/2014 6:10
01/23/2014 7:00
I have attached a file where I need to clean up column A but still maintain the rows sheet one is what I have sheet 2 is what I am hoping to achieve.
I have a list of dates with an hour inside them . I am trying to remove teh hour from the field , and leave only the date. teh format option just hide teh hour but not removing it how can it be done ?
View 4 Replies View RelatedI have a pretty good size file here where I need to remove duplicated date BUT keep only the Newest Record.
What is Needed is to look at "test Group Identification" and compare it to "Actual Start Time" and keep only the Newest Record and remove any Exact Duplicated data
See example of Data.
Test Status
Test Group
Test Group Identification
Actual Start Time
Want I need
Completed
CMTS
cdn01.bellsrd.va.richmond.comcast.net
09-Jan-2013 15:48:25 EST
Delete
[Code] .....
i'm trying to form a macro that allows the user to enter a date and have excel auto filter for all dates prior to (or ON and prior to if it makes it easier) that date, and delete all rows with that auto filter criteria in place. i'm working with a old auto filter macro i put together, but cant seem to get the criteria to work with, say, <7/14/2007 for example.
Attribute VB_Name = "modDeleteVal"
Sub DeleteRowByValue()
Attribute DeleteRowByValue.VB_Description = "Macro recorded 7/9/2007 by KHenzel"
Attribute DeleteRowByValue.VB_ProcData.VB_Invoke_Func = "V
14"
'
' DeleteRowByValue Macro
' Macro recorded 7/9/2007 by KHenzel
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else...
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I have this sheet for a search of data with two conditions (K4 and L4) to give me the minimum value in E column. Its working great but i need something more. Sometimes the minimum value in E column (Cons.) is the value of the date in J4, and i need some condition, if possible, in the formula on I7 that gives me the same result that it gives me now but without considering the data of the date in J4.
View 3 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 lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date
View 2 Replies View Relatedis there any way to remove/deactivate the cell's auto complete year function?
eg: input the value "03-jan" and the cell will automatically convert the value of the cell to 03/01/2008
I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
View 7 Replies View RelatedIn the enclosed SS there are several columns, "A" being Style and "G" being Available. I'm trying to piece together some code that will remove all of a style(all sizes) if the number of stock is less than say 3; however, if any one style has 3 or more available the entire style needs to remain in the spreadsheet.
I've been trying to piece together some code but I don't think I've quite come up with what I'm looking for.
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
Highlighted rows would be deleted:
Sheet1
*
A
B
C
D
E
F
G
1
Item
*
*
*
*
*
Imported
2
112
a
b
c
d
*
Apr-5-2013 08:53:19
[Code] ..........
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
I'm hoping to achieve the result in the image below.
Column A contains data, column B contains values to be added to column A and column C contains values to be removed from column A. Using formulas or macros, how can I present the result in column D?
I have a table comprised of 52 weeks (7 days each week) totaling 365 days. Here's what one week only
looks like
Week 2
Sun.
Mon.
Tue.
Weds.
[Code]....
I keep the prior year in a separate sheet in this same workbook. I need a formula or function or something to search the prior years table and pull the last years sales and last years customer count from the prior years sheet based upon the date.
I have a spreadsheet with dates filled in all the columns, in the first row. How can I use VBA to bring up a Pop up Calendar so that when I click on a date, it will take me to the cell with that date?
View 9 Replies View RelatedI need to find month / year and count the number of times it occurs..
This is what I have so far..
=SUMPRODUCT(--(main!D:D="name"),--(main!M:M="dormant"),-- (ISNUMBER(SEARCH("??????????",main!h:h))))
the problem is that the date column i'm searching had the following format: 28/1/2009 11:37:00
what do i need to replace the ????????? with to to make it look for the month and year only from the format above.
I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?
Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.
The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.
Below is an example from the date/time coloumn:
12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10
This is my input:
Col A
asde,dsfts-qwsa/001,asdasd-qwsa,asdas
etert,ttyutt-tww/013,dgdd-tww,dsfsaa
.
.
.
.
.
Col B
qwsa
tww
In Col C.. I want the output to be as
asde,asdas
etert,dsfsaa
Requirement as a text:
I need a macro that removes the value in column A based on the value in Col B.
The data that the macro removes is always between the "," (comma).
I have a large spreadsheet that has some duplicates in Row Q. However, in column L there is a date. I want to be able to find the duplicates in column Q then delete the duplicate with the earliest date.
[Code] .....