CODEWITHSUNDEEP
excelexcel-formulavlookupvba
smathur2016
smathur2016

Reputation: 31

How to fill data in excel sheet where date lies between a series of date ranges given in another sheet ? Also, a particular column should match

I'm working on Social Survey project.Due to discrepancies in data I'm stuck at a certain place. The survey conducting volunteers were given tablets with unique IDs. On different dates, the tablets were used in different cities Sheet 1 one contains a list of around thousands of responses for which city names are missing and Sheet 2 contains a list of tablets in use in different cities on different dates.

Sheet 1

City      DeviceID   StartDate     EndDate
Delhi       25      21-08-2014  26-08-2014
Mumbai      39      14-05-2014  21-05-2014
Chennai     91      17-11-2014  21-11-2014
Bangalore   91      11-10-2014  21-10-2014
Delhi       91      26-05-2015  29-05-2015
Hyderabad   25      23-05-2015  28-05-2015

Sheet 2

S.Id  DeviceId  SurveyDate  City
203     91      15-10-2014  ?
204     25      24-08-2014  ?

I need to somehow fill up the values for the city column in Sheet 2. I tried using Vlookup but being a beginner to excel, was unable to get things working. I managed to format the string in date columns as date. But am unsure about how to pursue this further.

From my understanding, Vlookup requires that the date ranges to be continuous, with no missing values in between. It is not so in this case. This is real world data and hence imperfect.

What would be the right approach to this problem ? Can this be done with excel macros ? I also read up a bit about nested if statements but am confused being a beginner to excel formulas and data manipulation.

Upvotes: 3

Views: 1741

Answers (3)

Blenikos
Blenikos

Reputation: 743

There is two ways to do what you want. The first one is using vba and create a macro to do the job BUT you will have to iterate through all your data multiple time (n1*n2 loops in the worst case scenario where n1 and n2 is the number of rows in it's table respectively) which is really slow if you have a lot of data.

The other way is a little more complicated and includes array formulas but is really faster than vba because it uses the build in functions of excel (which are optimized already).

So I will use a much simpler example and you can use that as you wish on your data.

I have the following tables:

Table1

city ID start end    
A    1    3    5
B    3    4    6
C    3    5    8

Table 2

ID point  city
3    5      ?

So we want a formula that completes the second table. where ID match exactly and point is between start-end. We are going to use MATCH and INDEX to get it. Here it is:

=INDEX(A$2:A$4;MATCH(1;(B$2:B$4=G2)*(C$2:C$4<=H2)*(D$2:D$4>=H2);0))

First of all to run this after you write it you should not press enter but instead ctrl+shift+enter to tell excel to run it as an array formula otherwise it will not run at all.

Now we got that out of the way let me explain what is going on here:

The MATCH does the following: match the value 1 (TRUE) in the range I created and that should be an exact match. But how the range is created? Lets take that part for example:

This B$2:B$4=G2 -gives-> {1;3;3}=3 --> {FALSE;TRUE;TRUE}

Similarly the second thing in the MATCH gives: {TRUE;TRUE;FALSE} So now we have (keep in mind that the * is similar to logical AND): 

{FALSE;TRUE;TRUE}*{TRUE;TRUE;FALSE} --> {FALSE;TRUE;FALSE}

and this combined with the third gives {FALSE;TRUE;FALSE}

So now we have MATCH(1;{FALSE;TRUE;FALSE};0) --> 2 because in the range only the second row matches the 1 (first row that it matches).

So now we just use index to get from another range whatever is on row 2.

You can use the above on your own data to get the expected results. Good luck!

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Here we use MEDIAN() as an easy way to test for "in-between".

Sub FillInTheBlanks()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim N1 As Long, N2 As Long, i As Long, j As Long
    Dim rc As Long, DeId As Long, sDate As Date
    Dim wf As WorksheetFunction

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set wf = Application.WorksheetFunction

    rc = Rows.Count
    N1 = s1.Cells(rc, "A").End(xlUp).Row
    N2 = s2.Cells(rc, "A").End(xlUp).Row

    For i = 2 To N2
        DeId = s2.Cells(i, "B").Value
        sDate = s2.Cells(i, "C").Value
        For j = 2 To N1
            If DeId = s1.Cells(j, 2).Value Then
                If sDate = wf.Median(sDate, s1.Cells(j, "C").Value, s1.Cells(j, "D").Value) Then
                    s2.Cells(i, "D").Value = s1.Cells(j, "A").Value
                End If
            End If
        Next j
    Next i
End Sub

Sheet2:

enter image description here

starting from Sheet1:

enter image description here

Upvotes: 1

Miqi180
Miqi180

Reputation: 1691

If the deviceId values should match and the survey date should be between the start date and end date, VLookup won't suffice. The following pointers, however, should get you started:

1) Define the date ranges from which the date comparisons should be made.

2) Use an overlap date checking function to determine if the date in question overlaps the start and end dates.

3) Loop through the date ranges and insert in Sheet2 when a match is found, i.e. when the deviceId values match and the date overlaps.

The following function takes as parameters the date to be checked, the start and end date and returns True, if dateVal overlaps the start and end date:

Function dateOverlap(dateVal As String, startDate As String, endDate As String) As Boolean

    If DateDiff("d", startDate, dateVal) >= 0 And DateDiff("d", endDate, dateVal) <= 0 Then _
        dateOverlap = True

End Function

Example usage

Debug.Print dateOverlap("05-10-2016", "01-10-2016", "10-10-2016") (returns true).

Upvotes: 1

Related Questions