Compare 2 Sets Of Data According To Start And End Date Column?

May 26, 2014

In my attachment I am trying to identify rows within Table 2 that do not fit in with Table 1. For example in Table 2, Row 16 has a Start Date of 17.11.2010 and an End Date of 13.11.2011 for ID number 151. This record has to be shown as "OK" because it fits into the date range of Row 103 of Table 1 i.e. Table 1 Row 103 has a Start Date of 01.01.2010 and an End Date of 28.02.2013 for ID 15. In other words the Start Date of Table 2 Row 16 is GE the Start date of Table 1 Row 103, and the End Date of Table 2 Row 16 is LE the End Date of the same Row 103 of Table 1, therefore this reocrd is OK.

Row 14 of Table 2 needs to be shown as "NOT OK" because it has a Start Date of 12.01.2013 and an End Date of 31.03.2013 for ID 15 and this doesn't fit any date range of any Row in Table 1 for ID 15.

View 2 Replies


ADVERTISEMENT

Compare 2 Sets Of Data Each With 2 Columns?

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

Compare 2 Sets Of Data To Change The Values

Sep 5, 2007

I am a absolute newbie to VBA, I've got 2 timetables, they show deliveries for different countries. I need to make my code as DYNAMIC as I can. TableCustomer shows the customer's name, country and their opening times, Saturday - Friday, it uses 1s and 0s to determine if they are open or closed, 1 = open, 0 = closed, so the headers are: Customer Name, Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc.

TableCompany shows the days of deliveries made to each country by the company, using 1s and 0s, 1 = Deliver, 0 = Don't deliver. The headers are Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc. An example here is that, if in TableCustomer, the customer is opened(1) on a Saturday, but in TableCompany, the company does not deliver(0) to that country on the Saturday, change that 1 into a 0 in TableCustomer. But if the Customer is closed(0) on the Saturday, then don't change the value 0, because the company will not be able to deliver to them anyway, even if they can deliver to that country on that day.

View 3 Replies View Related

Vlookup To Compare Two Sets Of Data And Change The First Set Of Data If It Is Than The First

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

How To Compare Two Sets Of Dates Against One Another In VBA

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

Compare Date: Compare The Date From The User Input And The Date Listed

Jan 18, 2007

I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))

Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")

Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)

'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................

View 2 Replies View Related

Average From Whole Months Data Taking Exact Date To Start With

Jan 7, 2014

In the file attached there is only the data to look at, though file is little bit lager.

Now in cell G10 I've got the stock forecast in 3 months time, this stock forecast is calculated from a date rounded to the closest month (according to my criteria), then the formula looks at the reference number cell D1 to work out the formula. All the steps are in the file.

In order to be more accurate my boss and I want to use the exact date, so that delete all the round month process. My intention is to use the day of the date and then make a fraction of the months to calculate the average. Taking into account that a month have as an average of 30.4166 days.

In the file I did an example, to clarify my messy explanation.

Cells B2, C2, D2 won't be needed in order to do so.

Please have a look at the file. At the end my intention is to to such a formula or a similar one. The formula should be an automatic one when typing dates in cell A10

The result of it is in row 31 and 32.

trial.xlsx

View 1 Replies View Related

Excel 2010 :: Data Validation Won't Accept Start And End Date From Cell Values?

Mar 16, 2014

Data validation in an Excel 2010 workbook.

I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.

As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.

But I can enter 1/9/2020 and the Data Validation happily accepts that date.

View 12 Replies View Related

Compare & Confirm Dates In Column To Date In Cell

May 21, 2008

I am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.

Is As follows

Dim rowNum As Integer, colNum As Integer, currCell As Range

rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If

View 4 Replies View Related

Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays

Nov 15, 2008

First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...

View 7 Replies View Related

How To Auto-Populate Date Range (working Days Only) From Start Date And End Date

May 25, 2014

I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 1 Replies View Related

Compare Data By Date?

Dec 7, 2013

I'm looking for a formula which compare total sales or customer count by date and It should automatically get updated daily.

For an example In my historic Data I have details of the sales from 1st to 31st of December 2012. Also I'm updating the present details day by day. Is it possible to compare the total sales from 1st to 7th of december 2012 to the total sales from 1st to 7th of 2013 automatically and then tomorrow it will compare from 1st to 8th 2012 to 1st to 8th in 2013 and will go on until the end of the month?

View 8 Replies View Related

Calculate Next Due Date Based On Start Date Frequency And Current Date?

Mar 9, 2014

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)

View 4 Replies View Related

Multiple Date Sets Filter

Aug 21, 2014

I have six columns.

Column A: dates
Column B: data with relates to A

Column C: dates
Column D: data with relates to C

Column E: dates
Column F: data with relates to E

View 1 Replies View Related

Delete Both Sets Of Duplicate Date?

Oct 8, 2008

I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data? For example, say this is what is in my spreadsheet:....

I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.

View 4 Replies View Related

Matching Two Sets Of Date And Displaying

May 8, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets and if they match enter in column D of the referral sheet the month they were seen but only if its a 1st contact (appt type on column D of contact sheet)

ABCDReferralIDReferral Date409383316 January 2012 Month / year of referral Month of first contact
485955005 March 2012January 2012539372005 September 2011March 2012385048229 February 2012
September 2011483172202 December 2011February 2012944156617 January 2012December 2011
393039322 February 2012January 2012

ABCDContact IDcontact Datemonth / year of contactAppointment Type
539372026 January 2012January 20121ST385048205 March 2012March 20121ST944156616 December 2011
December 2011F/UP222944119 December 2011December 2011F/UP344055529 December 2011
December 2011F/UP539372010 September 2011September 20111st

View 8 Replies View Related

Compare Data If Data Is Changed Put In Column In New Sheet

Jun 9, 2007

I want to import data with a web-querie. The data consists of numbers in a column (14 cells).

I want to poll the info in the table in the webquerie every 15 minutes to see if the info in the table is changed. Therefore I refresh the info every 15 minutes, and when the info is changed, than the data must be put in a new sheet in a column and the columntitle must be the date and time that the info is put into the column. Can this be done in vba? I suppose that to compare the info of the new poll with the info of the previous polling the previous info must be put in a "compare" table.

View 4 Replies View Related

Data Compare And Result In Another Column

Apr 25, 2012

I have DAta in Col A (Med ID ) and B has number of events assocaited with the unique ID. The Unique ID is also in Col C from another data source. I wnat to return the value from Col B in in Col C that mathces the MD ID if available. There are several more col in the sheet that have other dta that is not assocaited with this. HAving an Excel formula or VB code will do some analysis. Example

Exported Med ID Data Source 1Exported Med ID Data Source 1 Number of Times Not Availle able Med ID- Matched Result From Col B to Med Id in C

View 9 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related

Calculate Start Date From Finish Date Minus 14 Working Hours

May 7, 2014

I needs a formula (not VBA) to calculate the required start date.

I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00

My working week is from monday u/i friday and every day I work from 08:00-16:00

At what time do I have to start the job to get it done in time.

The formula should give this result: 01-may-2014 15:00

View 3 Replies View Related

How To Auto Populate Date Range (working Days Only) From Start And End Date

May 26, 2014

i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 4 Replies View Related

Automating Out Current Loan Period From Start Date And Today Date In IMPT Function?

Jun 12, 2014

Trying to automate the period part of the impt function

To calculate current value of loan i have the below formula below with the 3 being the current period

=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)

What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.

View 4 Replies View Related

Compare Data Of Two Columns And Want Result In One Column

Apr 3, 2014

I have one column which contains suppose first names & i have other two column which contains first name & last name in same sheet but like they may be having in g & h column.

so i want to exact last name of user form that column(g & h) to my first name column(a).

View 1 Replies View Related

Vbcodes To Select Start Date Then Begins Highlight Until End Date

Oct 28, 2006

i need VB to select the start date of booking and autohighlights the "blank booking slot cells" until the end date that is input in Excel. You can visualise this as a room booking system. The left most column is the dates and the first row represents the different rooms type. E.g. if one guest books the Twin Deluxe No. 123 room on 23 Oct 2006 until 25 Oct 2006. We wish the VB can essentially help us identify the start date then begins highlighting the three slots for 23, 24 and 25 Oct on the column for the Twin Deluxe Room 123.

View 4 Replies View Related

Calculate End Date From Start Date & Number Of Weeks

Jun 26, 2007

Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?

View 5 Replies View Related

How To Compare Data Of Two Columns And Show Number From A Third Column

Apr 16, 2014

How can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample

View 4 Replies View Related

Single Column With Multiple Rows Of Data - Compare Names

Apr 12, 2012

I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.

I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.

So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.

View 2 Replies View Related

Calculating Number Late Last Week From Multiple Dates / Date Sets

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

Counting A Column Based On Two Sets Of Criteria

Jul 23, 2009

I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.

View 4 Replies View Related

Highlight-Select Start Date To End Date

Oct 28, 2006

We are doing a project on a booking system and wish to use VB codes to autohighlight the slots from the start date until the end date.

View 9 Replies View Related







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