How To Sort 2 Sets Of Data With Dates To Correlate Each Other
Jun 30, 2009
i got 2 sets of data, but the dates are not correlated
For e.g.
3/18/199610 3/18/199613.5
3/19/199610.1253/19/199614
3/20/199610.1253/22/199614
3/21/199610.25 3/25/199614.0625
3/22/199610.25 3/26/199613.25
3/25/199610.25 3/27/199613.1875
3/27/199610.25 3/28/199613.125
3/28/199610.25 3/29/199613.5
3/29/199610.25 4/2/199613.625
4/1/199610.3754/4/199613.8125
as u can see the dates jump here and there
is there anyway that i can sort it out so that the dates would correlate to each other on the same rows?
if its possible, i have like 16 sets of data to correlate, how do i do it?
View 10 Replies
ADVERTISEMENT
May 1, 2013
I have two sets of data:
Data set #1
Indicator PriceIndicator Price Date
2.1 10/27/08
2.11 10/22/08
2.17 11/21/08
2.38 03/20/09
2.38 03/25/09
2.46 03/19/09
2.5 03/09/09
2.5 03/24/09
2.53 12/04/08
2.73 12/09/08
2.82 12/24/08
2.83 12/18/08
2.89 12/12/08
2.9 03/13/09
Data set #2:
Close Price Close Price Date s1s2s3s4r1r2r3r4
2.25 12/11/09
2.30 12/12/09
2.40 12/13/09
2.00 12/14/09
2.12 12/15/09
2.50 12/16/09
2.51 12/17/09
2.53 12/18/09
2.49 12/19/09
What I'm attempting to do is find what are the CLOSEST four indicator prices in data set #1 are above (r1, r2, r3, r4) and below (s1, s2, s3, s4) the closing price in data set #2. Also, I can only use the indicator numbers in data set #1 that are on or before the close price date in data set #2. Because of this, not all of the r's and s's will be filled in.
I would like to use just regular excel formulas, but I have a feeling that VBA may be my only option.
View 3 Replies
View Related
Mar 4, 2014
I have a table as below, and I would like to keep only the latest date for each EMP_NO. For example:
001VICE PRESIDENT A062004-01-01
031SENIOR VICE PRESIDENTA042004-01-01
...
....
....
=======================================
[EMP_NO JOB_TITLE DEPTCHANGE_DATE
014 ASSISTANT VICE PRESIDENT A01 2004-01-01
001 MANAGER A062000-03-01
001 MANAGER A062001-06-01
001 VICE PRESIDENT A062004-01-01
076 VICE PRESIDENT A022003-04-23
013 DIRECTOR OF MARKETING A01 2000-06-19
031 FIRST VICE PRESIDENT A042002-01-01
031 FIRST VICE PRESIDENT A042003-01-01
031 SENIOR VICE PRESIDENT A042004-01-01
=======================================
View 5 Replies
View Related
Jun 11, 2014
I am trying to figure out how to compare two sets of dates against one another in vba. The data is spread over eight columns. I need to be able to find the smallest date in the first four columns and then compare it to the smallest date in the other four columns, then go to the next smallest and so on.
View 4 Replies
View Related
Oct 4, 2011
Based on the table below, I need two tasks.
1. Sort out the data descending for dates
2. Have a way so that I can only retrieve the latest information.
Meaning if I ask for Pen, I should only get a value of X
Similarly for XYZ, I should only get a value of N
Date A B Types
Value 5 Oct Pen X 5 Oct Pencil Y 5 Oct Pencil H 4 Oct Glue
Q 1 Oct
Pen N 27 Sep
Glue T 2 Sep
XYZ N 2 Sep
XYZ X
View 4 Replies
View Related
Jan 17, 2014
I have 3 different series: S, J, and D. Each series has a list of different dates, and I need to plot the results for each date, while comparing the 3 series. I have created 3 different charts to display the results for each series - list of dates. However, I'd really like to chart all 3 series on one chart, so that you can see how the 3 compare.
I believe my obstacle is that the list of dates are not consistent between the 3. I tried to get around this by creating one table with all the dates, and leaving blanks where necessary (on Sheet 2). However, this created a problem because the blanks showed on the charts as 0s.
View 5 Replies
View Related
Jun 4, 2009
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot.
Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range.
For example:
In A3 type "1/1/2006"
In B3 type "1/1/2007"
In C1 type "6/30/2005"
In C2 type "6/30/2006"
Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is.
Is this possible with a formula?
View 10 Replies
View Related
Jul 2, 2014
I am putting together a simple table to display current week's data vs previous weeks. The current week's data is drawn from a status chart which changes frequently. The constant change is fine for 'Current' as I only want the current data displayed.
The problem I am having is calculating the number of late jobs that existed during the previous week.
The status log has a due date which is compared to the current date to determine 'on time' status for the current week.
Due dates are reissued regularly so I can't use
=COUNTIF(RANGE,WEEKNUM(NOW()-1)) to return data about last week from my status chart.
I have available a 'Movement Log' (in the workbook but a separate worksheet) which tracks the changes in the due date field, but I'm not sure how to integrate that data to calculate the # of jobs that were running late from the last week.
My thought is that I need to perform a count of the # of late based on a comparison of 'due date' to 'date of the last day of last week' with a way to insert the "old due date" from the movement log to replace what is shown in the status log if necessary.
Movement Log.JPG
Status Chart.JPG
View 1 Replies
View Related
Sep 14, 2006
I've searched the forum prior to posting, but have not found exactly what I need and am not familiar enough with macro code to modify based on an existing post.
Here's what I'd like to do: I have a worksheet with columns "A" through "J" which contain production scheduling information. Column "H" contains an estimated completion date - I need a macro to sort the rows base on the completion dates (in ascending order) when I request the macro to run - I'm attaching the macro to a button control. One concern is, the column contains some excel default dates of 1/0/1900 that I don't want to include in the sort because they should stay at the bottom of the sheet, rather than at the top. Any rows with the 1/0/1900 don't yet contain information, only the default date based on the calculations.
View 6 Replies
View Related
Apr 10, 2008
Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 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
Jul 13, 2013
I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
View 1 Replies
View Related
Oct 5, 2009
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
View 9 Replies
View Related
Jul 8, 2014
I have two sets of data for students. One set contains all the students with certain test scores, taking up columns A to N. The other set contains about 80% of those same students with a different set of test scores. Students are sorted by ID numbers. How do I combine the second set into columns O to V so that student ID numbers match and it inputs the rest of the student data into the sheet.
I know there will be some blanks because not all students will be there but I need them to match up, even with the blanks.
View 2 Replies
View Related
Jul 10, 2014
I created a macro that extracts two columns of numbers from one workbook and need to match them to another set of numbers in another workbook. For instance i have numbers like...
18314 907
18272 64
11005 907
11005 324
..Now i need to match these numbers up with the others to find the hyperlink associated with them.
View 5 Replies
View Related
Nov 8, 2013
I'm attempting to create a mailing list for the company I work for, but the software that has the data record is very limiting. The two tables I received from the program are listed:
Customer
Number of Sales
name 1
#
name 2
#
name 3
I tried to illustrate that the address list has a different customer composition than the sales list; the address list has customers that the sales list doesn't have. This means I can't just sort alphabetically and drag the addresses over. My goal here is to create a datasheet that incorporates both the address and number of sales so that we can send the top 500 customers a mailing. How can I do this?
View 2 Replies
View Related
Apr 1, 2014
I have 3 seperate tabs of data that i want to put into a combined sheet, how would i do this, i assume it's just like a copy/paste exercise but not sure how to do this using VBA?
My tab names are;
FND Data
UL Data
Life data
I just need the entire range copying from each tab into the 'Combined' tab
View 5 Replies
View Related
Dec 13, 2009
In the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.
It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.
View 2 Replies
View Related
Jan 21, 2010
I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
View 13 Replies
View Related
Dec 6, 2006
I manually reconcile two sets of data every month by sorting by PO# and then manually matching up the onse that are the same and then calculate if there is a variance in $ amount.
The 1st set of data is a list of invoice PO#'s and invoice $ amounts and the 2nd set of data is order PO#'s and order $ amounts. What I need to do is reconcile the orders to the invoices by PO#. It is very common to have a PO# on each list but have a different $ amount and I only need to know what the difference is between the order amount and the invoiced amount. I also need to know what is on the invoice list and not on the order list and vise versa....
View 10 Replies
View Related
Feb 23, 2009
I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do: ....
View 9 Replies
View Related
Aug 28, 2009
I'm trying to compare two sets of data on the same sheet. Each set has 4 columns...A - D and F - I. I would like to take the data from D and I (range 20 to 50) and compare them to see if any differences exist in the two columns. If there is something different I would like to bold and highlight the cells (A-D) in that row, and the same goes for the second set of data (F-I).
I'm trying to do this with macros so I can add it into my existing code.
View 9 Replies
View Related
Mar 17, 2008
I currently have 5 dates listed, and formatted as dates. I update this list so under the 5th date I will add a sixth date. The next thing I want to do is sort these dates from newest to oldest.
Problem #1- It is not sorting these in the correct order. Example:
3-11-08(appears as) 2008-03-11(Written in box up top)
12-5-08
3-17-08
2-25-08
1-13-08
1-10-08
Probelm #2- sometimes the dates will not even give me the option of sorting from newest to oldest.
View 9 Replies
View Related
Mar 4, 2010
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
View 9 Replies
View Related
Nov 10, 2013
I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.
I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.
Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.
The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.
Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...
T3OCcxw.jpg
example.xlsx
I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result
VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range
[Code].....
View 1 Replies
View Related
Jul 8, 2009
Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
View 5 Replies
View Related
Oct 28, 2009
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
View 13 Replies
View Related
Apr 3, 2013
Create one X-Y SCATTER graph (with Smooth Lines and Markers) with the Average Movement Time (MT) on the Y axis and the Index of Difficulty (ID) on the X axis for each group member during STATION 1: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK and STATION 4: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK: WEIGHTED
ID stands for index of difficulty, the graph is supposed to indicate that as the index of difficulty increases (3) movement time increases and as it decreases (2) movement time decreases.
Lab 8 Data Q 4 attempt.xlsx‎
View 5 Replies
View Related
Nov 9, 2011
I have horizontal records of data , datewise, if i need to find output for a particular date what should be formula, for instance for 1/21/2011 output is 135 nad for 1/15/2011 is 400. I need a formula wherein if i put the date it should throw the output of the same.
1/10/20111/11/20111/12/20111/13/20111/14/20111/15/20111002001501203004001/16/20111/17/20111/18/20111/19/20111/20/20111/21/20116075901051201351/22/20111/23/20111/24/20111/25/20111/26/20111/27/2011253545556575
View 3 Replies
View Related
Aug 22, 2012
Create a macro or some other solution to sorting my data. Just clicking on Sort Ascending isn't good enough because it'll sort everything and remove the empty rows. However, I want the empty rows to stay there. The way that I've been doing this is manually. Basically, after the empty rows were inserted, I'd click on a cell in Column A and click on Sort Ascending, then I'd scroll down and click on the next set of data and click on Sort Ascending, then the next set and the next. I'd end up having to do this hundreds of times.
I've uploaded an example of what I'm talking about, I also labeled the tabs as "I want this" and "to look like this":
Example file.xls
View 5 Replies
View Related
