Cleaning Data To Match Dates
Jun 21, 2006
I have several time series in excel pulled from a datasource on a daily basis, however some series include weekends, some dont and some others have a few days missing. What I did is I already have a column set up with only weekdays on the other sheet, what I am trying to do is match the data with the dates.
The obvious choice is vlookup, but given that I have over 50 time series and the time span is over 10 yrs, its not the optimal solution...it will crash the sheet i think. I also tried a combo of match and index...but same...too many formulas. I think I need a macro. Does anyone who analyzes time series have a "cleaning" code that he oe she can share with me?
View 9 Replies
ADVERTISEMENT
Jun 24, 2008
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).
I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!
View 9 Replies
View Related
Apr 26, 2014
However I have survey data results and in one of the cells it has multiple values which are separated between ; and some are not separated at all e.g B&Q; The Range; Wicks The Garden Shop
Also there are spelling mistakes everywhere and variation of the word B&Q e.g b+q, B n Q
I need to add count up all of the B&Q, Wicks etc...
View 7 Replies
View Related
May 10, 2006
how to split data into columns.
I have a set of (imported) data consisting of 1000+ rows. The data set (bank statement) contain six informations (date1, transaction, vendor, date2, sum1, sum2) in one cell which has to be split into six columns.
Using text-to-columns will generate more than six columns since the set dosn't have any delimiters and both the transaction and vendor name-fields contain blank spaces.
Is there a way to either split the data into the correct number of columns or if text-to-columns is used - a way to manipulate the data into the correct columns, for instance with help of filtering ?
small data sample included
View 3 Replies
View Related
Dec 22, 2008
I am basically doing some housekeeping and trying to reduce my number of code lines.
Can this bit of code be done a bit simpler?
For r = lrow To 15 Step -1
If Range("N" & r).Interior.Color = vbRed Then
Range("N" & r).Interior.Color = vbCyan
Range("N" & r).Value = "Triangular"
End If
If Range("V" & r).Interior.Color = vbRed Then
Range("V" & r).Interior.Color = vbCyan
Range("V" & r).Value = "Triangular"
End If
Next r
View 9 Replies
View Related
Apr 15, 2007
It has been working perfectly from all sites but for some reason started to enter the wrong data once in a while?
Sub Macro8()
Application.StatusBar = "Updating Data from NOAA"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Web Data").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.srh.noaa.gov/ifps/MapClick.php?TempBox=1&HumidBox=1&Submit=Submit&FcstType=digital&textField1=32.74&textField2=-96.45&site=fwd&Radius=0&CiTemplate=0" _
, Destination:= Range("A15"))
.Name = _
"MapClick.php?TempBox=1&HumidBox=1&Submit=Submit&FcstType=digital&textField1=33.65&textField2=-95.54&site=fwd&Radius=0&CiTemplate=0_5"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False............................
View 7 Replies
View Related
Aug 31, 2007
I am using VBA to enter customer information into a Microsoft SQL database. I have an excel sheet with thousands of customers listed. I will be using a While loop to process each line of the sheet. Part of the While loop needs to make any format changes necessary before the data moves over to the DB. So here is the issue:
How do I turn the following phone numbers:
(410) 273-9200
910-944-1232
276 623 4254
410 612 1100 Rob
(413) 786-1636 Cindy
1-800-783-4661
304) 842-5491 Sherry
Into a uniform layout? I would prefer ###-###-####
Each of the numbers above is an example from the list I have. There may be more variations (I have over 20 seperate lists to process, with over 10000 customers per list)
I imagine I need some process that will remove all character except numbers and then break the numbers up to add the hyphens back in at the appropriate places.
View 10 Replies
View Related
May 15, 2007
I frequently use the record option to start off my macros. I realize this usually results in recorded events which are not absolutely necessary (recording a print macro is one that is full of excess line items). I then try and combine what I have recorded with what I have garnered from this wonderful website. If my spreadsheets are small - and I am not writing code to pass any inspections - is there a need to go back and clean up?
View 2 Replies
View Related
Jun 22, 2014
I have an old customer list maintained by my company's previous owner in excel format that mostly stored numbers ###-#### format, but also some in ###-###-#### format.
My new list has always been maintained with numbers in (###) ###-#### format.
I have merged these two lists in a new excel file have begun the tedious process of cleaning up duplicates and bad data.
I have used conditional formatting to highlight duplicate numbers in the appropriate columns (L, M, N, O) in this case. However, it does't find matches in the other formats or perhaps the number with the old area code. I know the area code change could lead to false positives, but they should be minimal as this is a small business with mostly local customers. So what I'd like to find out is a formula for conditional formatting that will compare the last 8 characters in each cell (###-####) so as to eliminate issues of missing or different area codes. I would like it to check all of the columns for duplicate numbers not just within each column or within each row individually.
Also separate but related if possible to highlight a different color any cells with data not in any of these formats or if not in (###) ###-#### format if it's too much for all the formats.
View 8 Replies
View Related
Feb 2, 2012
I wanted to create a target workbook that is a subset of the source workbook
1) I want to specify a list of worksheets in the source workbook:
For Each WSCurrent In Sheets(Array("SheetA", "SheetB", "SheetC"))
Next WSCurrent
2) I want to them copy these sheets into another workbook (don't know how to code this)
3) I then want to hardcode all these sheets (I don't know the most efficient way to do this)
4) Lastly, I want to eliminate certain columns (can be fed through a hardcoded list of Columns to delete e.g. X, W, Z)
View 5 Replies
View Related
Mar 18, 2013
Is there a formula where I can take a zip code, and for any zip codes that have the 4 digit zip code extension, to remove those 4 digits, and to add a zero to the beginning of any zip codes where the zero was cut off. Is there a formula I can run my zips through to do both of those tasks?
View 1 Replies
View Related
Apr 26, 2007
This is the current code I am using, it works but from another post it looks like it could be cleaned up.
With Sheets("Web Data")
.Activate
Range(.Range("A6")).Offset(2, 1).Resize(2, 24).Copy
Sheets("MW Forecast Calculation").Select
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is the code I am trying to get to work but I am having trouble with the red text.
Set CopyRange = Sheets("Web Data") Range(.Range("A6")).Offset(2, 1).Resize(2, 24).Copy
Set PasteRange = Sheets("MW Forecast Calculation")
Cells ("C9:Z10")
CopyRange.Copy PasteRange.Values
View 5 Replies
View Related
Jan 15, 2009
I am wondering if there is an easier way to look up values in a matrix. I use the VLOOKUP with the vertical values as the lookup_value, then another lookup for the col_index_number to find the horizontal values. The example in the attached file is just a sample of my spreadsheet. I need to apply this to a roughly 10*10 matrix (which changes every now and then) and apply this matrix to over 1,200 rows. Is there an easy way to returne theses values or is the way I am currently doing it the best option.
View 3 Replies
View Related
Sep 26, 2012
I have a worksheet that contains lots of value. I want to run quick calculation on it but for some reason counta function counts blank cells.
I checked and the cells with =len() and it returns a number > 1
What is the proper way to clean the whole worksheet with some copy paste value and get rid of invisible character like space or nonprintable ones to be sure to get the right number of cells with value?
View 9 Replies
View Related
Mar 7, 2013
My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually )
The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)
The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.
Capture.PNG
What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.
View 5 Replies
View Related
May 14, 2014
I have a data table with dates and marks by 0 and 1.
I need a formula, if a date selected from data table, to find first next or previous matched date.
At attached table you can see all detail and explanation.
match dates.xlsx‎
View 5 Replies
View Related
Feb 17, 2009
I have an array set up like so: ....
View 13 Replies
View Related
Feb 12, 2010
how to go about this, as I always get confused with Index and Match. In my example, in col A I have the Beginning of a period, Column B is the End of the Period, and the amount to be matched is in Column D. Range A1 to E4 is the source data. Below that, starting from A9 to A37 are dates. I would like to match the corresponding value in range D2:D3 if the date in range in A9 : A37 falls between the period stated in A2:B3. I've listed how the values should appear in C9:C37.
View 2 Replies
View Related
Mar 19, 2014
I've been trying for a few time to make a sub, on VBA, that writes, in a specific column, " " or 1 depending on the date that is inserted. The thing I can't quite do is:
1 - I have a sheet where the different projects of a company are stored (name, starting date, ending date and person responsible) and where new projects can be added from a UserForm
2 - I also have another sheet were costs from the projects are stored (one line with cost 1 from project 1, another with cost 2 also from project 1 and cost 3 from project 2, etc) and where I can also add a new cost with another UserForm (this cost as also a date that is added, to when it happened)
3 - What I need to do is, on the costs' sheet I need to create a sub that in the last column adds me 1 if the date of the cost added is not a date between the starting and ending date of the project that it corresponds.
View 1 Replies
View Related
Dec 2, 2009
I am trying to use the .Find method to match a date. In sheet2 I have a range of cells which are formatted to only show the day "dd" (I could format each cell to show (dd-mmm-yy, etc). In sheet1, I have dates which are formatted as "January 7, 2009"
I want to create a VBA find routine which allows me to match the date from Sheet2 (Ranged between cells A1:Z1) with Sheet1. Then output a MsgBox stating "Match found in cell $A$7 of Sheet1" I have tried a formula similar to:
PHP Set FoundCell = Sheets("Sheet1").Range("A1:Z1").Find _
(what:=DateValue("January 7, 2009") ,lookin:=xlFormulas)
View 5 Replies
View Related
Jan 8, 2010
See my attached spreadsheet. I am trying to get start dates and end dates based upon rates. The first start date is really simple, I just use A2. In F2 should return the last date from A2:A27 where the rate is I2. In E3 The I would like to return the first date in A2:A27 where the rate is G3. and so on. If the rate is 0 then the date can be blank or 1/1/1900.
View 2 Replies
View Related
Mar 18, 2009
I am currently using the following formula and it gives the result P-10, P-10, P-11 below dates that are manually entered. I can not figure out why the date 10/31/09 returns a P-11 value when it matches a P-10 value? All other dates work expcept for the 12 period ending dates. Any help would be greatly appreciated.
=INDEX($B$5:$B$16,MATCH(A$1,$A$5:$A$16,1)+1,0)
10/17/09 10/24/09 10/31/09
P-10 P-10 P-11
01/31/09 P-01
02/28/09 P-02
04/04/09 P-03
05/02/09 P-04
05/30/09 P-05
07/04/09 P-06
08/01/09 P-07
08/29/09 P-08
10/03/09 P-09
10/31/09 P-10
11/28/09 P-11
01/02/10 P-12
View 9 Replies
View Related
Aug 8, 2014
I have attached a sample workbook which shows my desired output.
In E3 I need a formula to index B3:B60 and bring back the value where D3 falls between the dates in A3:A60
I have manually typed 46 into E3 as 15/05/2013 is equal to or greater than 13/05/2013 but less than 20/05/2013
Attached File : index match between dates FF.xlsx‎
View 4 Replies
View Related
Apr 6, 2013
what i am trying to do is look at an entire column, and see if that same date exists in the other Activity tab, if the dates match, then show the activity
here is the formula i am using, =IF($A9=Activity!$A2,Activity!$B2,"No Activity")
my problem is that i am only able to isolate one of the cells,, how do i check the entire column?
in other words, if i select two weeks, those two weeks of activity will show, if i select all weeks, all of the weeks activity will show, right now i can only get one of the weeks
my data looks like the following
1st tab
1/8/2013 - 1/14/2013
1/29/2013 - 2/4/2013
1/22/2013 - 1/28/2013
[Code].....
View 3 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Jul 18, 2012
I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
The formula I am currently using is:
=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))
Sheet 1 (Named: Main)
Number
Date
Formula
1
15/06/2012
[Code] ....
Sheet 2 (Named: Trees)
Number
Date
1
05/06/2012
[Code] .....
View 2 Replies
View Related
Nov 18, 2013
How do I auto fill a series of 2014 dates in the row below the weekdays? I have a row C5 that has a series of auto fill weekdays successfully for 2014. I can't seem to auto fill the weekdates in the row below. I have to make manual adjustments for each Monday, and for the correct month ends, etc.
View 8 Replies
View Related
Aug 17, 2009
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies
View Related
May 12, 2012
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
View 9 Replies
View Related
