Help regarding IF statement based on text and consecutive dates

[jmu2013]

I have been working on a spreadsheet on excel 2010 tracking the number of items a student can acquire. One student can have multiple teachers (uID). Items can be tested once or multiple times in a day. From columns A to E lists the raw data while column G contains filtered data of unique items while column H (=COUNTIF($B$2:$B$27,$G2)) is the number of times the item was tested for.

Now here is the really hard part. In order to be considered Completed (Column I), there are two options:
1. If an item is tested and acquired ("Yes" result on column E) by the student three times and on three different but consecutive dates (ex. 1/26 - 1/27 - 1/28 or 1/26 - 1/29/ - 2/5) and the testing is done by at least 2 different uIDs then the item is listed as Completed (column I).
2. If an item is acquired 5 times in the same day and tested the same uID, then the item is listed as Completed.

On DateMastered (Column J), it will only indicate the day the item was acquired based on the two conditions above.

Here are the IF statements I've been working on: I2 (=IF($E2="Yes","Yes",(IF($B2=$G2,"Yes","No")))) and J2 (=IF($I2="Yes",$C2,""))

	A	B	C	D	E	F	G	H	I	J
1	ID	Item	Date	uID	Acquired		Item	NumofInstances	Completed	DateMastered
2	1	lightbulb	1/26/2013	u0001	Yes		lightbulb	6
3	2	lightbulb	1/27/2013	u0001	Yes		lamp	3
4	3	lamp	1/27/2013	u0001	Yes		ball	3
5	4	ball	1/27/2013	u0001	No		hockeypuck	2
6	5	hockeypuck	1/27/2013	u0001	No		card	3
7	6	card	1/27/2013	u0001	Yes		soccerball	1
8	7	soccerball	1/28/2013	u0002	Yes		laptop	1
9	8	lightbulb	1/29/2013	u0002	No		pc	1
10	9	lightbulb	1/29/2013	u0001	Yes		pc1	1
11	10	lightbulb	1/29/2013	u0001	Yes		mirror	5
12	11	lightbulb	1/29/2013	u0001	Yes
13	12	laptop	1/29/2013	u0001	No
14	13	pc	1/30/2013	u0002	Yes
15	14	pc1	1/30/2013	u0002	Yes
16	15	lamp	1/30/2013	u0001	Yes
17	16	lamp	1/30/2013	u0001	Yes
18	17	ball	1/31/2013	u0001	Yes
19	18	ball	2/1/2013	u0001	Yes
20	19	card	2/2/2013	u0001	Yes
21	20	card	2/3/2013	u0001	Yes
22	21	hockeypuck	2/3/2013	u0002	No
23	22	mirror	2/4/2013	u0002	Yes
24	23	mirror	2/4/2013	u0002	Yes
25	24	mirror	2/4/2013	u0002	Yes
26	25	mirror	2/4/2013	u0002	Yes
27	26	mirror	2/4/2013	u0002	Yes

<tbody>
</tbody>

. The results that I'm getting are not correct but I think this is the right direction. I appreciate any help or advice.

 

[bruderbell]

So what should the results look like? Mirror is completed but nothing else?

And what do you mean for the DateMastered? Is the the first date acquired or the last or average?

 

[jmu2013]

Sorry, I haven't updated or responded to this thread. You are correct bruderbell, only mirror would be completed since it fits the criteria for the item being tested 5 times in one day by the same uID with all of the results being "Acquired (Yes)."

Date Mastered would be the last day the item was acquired when the item is listed as "Completed." For the mirror item, the DateMastered would be 2/4/2013.

	A	B	C	D	E
28	ball	2/5/2013	u0002	Yes
29	card	2/5/2013	u0002	Yes
30	hockeypuck	2/5/2013	u0001	Yes
31	lamp	2/6/2013	u0001	Yes
32	soccerball	2/6/2013	u0002	No

<tbody>
</tbody>

Lets say there was more info available. With this information, only ball and card were Completed and both were Mastered on 2/5/2013. Though lamp was "Acquired" on three different dates because it was tested by the same uID, it doesn't count.

 

[bruderbell]

Now here is the really hard part. In order to be considered Completed (Column I), there are two options:
1. If an item is tested and acquired ("Yes" result on column E) by the student three times and on three different but consecutive dates (ex. 1/26 - 1/27 - 1/28 or 1/26 - 1/29/ - 2/5) and the testing is done by at least 2 different uIDs then the item is listed as Completed (column I).
2. If an item is acquired 5 times in the same day and tested the same uID, then the item is listed as Completed.

Define consecutive...your first example is what I'd call consecutive dates, the second is not. Do you mean three consecutive testings, with no dates being the same?

 

[jmu2013]

Define consecutive...your first example is what I'd call consecutive dates, the second is not. Do you mean three consecutive testings, with no dates being the same?

Yup, sorry I was unclear. For the first condition, the dates don't have to be consecutive. As long as its three different dates that go in order for a specific item. The item has to be tested by two different uIDs with the three results being "Acquired." This will produce the result "Completed." The last date it was successfully tested on will be the DateMastered date.

 

[bruderbell]

Ok, this can be done with user defined functions. I wrote two (and enjoyed learning how to do it). There's probably other ways to do this that are more elegant, but this is working for me. Here is the code (you may want to google and read about UDFs to understand where the code goes):

Function completed(datatable As Range, item As Range)
n = datatable.Rows.Count
Dim ItemArray()
ReDim ItemArray(1 To n)
Dim DateArray()
ReDim DateArray(1 To n)
Dim uIDArray()
ReDim uIDArray(1 To n)
Dim AcqArray()
ReDim AcqArray(1 To n)

'This section populates the VBA arrays that this formula uses to solve the problem.
'Note that it only populates dates if the item from the table matches the item requested.
For i = 1 To n
AcqArray(i) = datatable.Offset(i - 1, 4).Resize(1, 1).Value
ItemArray(i) = ((datatable.Offset(i - 1, 1).Resize(1, 1).Value = item) And (AcqArray(i) = "Yes"))
If ItemArray(i) = True Then
    DateArray(i) = datatable.Offset(i - 1, 2).Resize(1, 1).Value
    Else
    DateArray(i) = 0
End If
uIDArray(i) = datatable.Offset(i - 1, 3).Resize(1, 1).Value
Next i

'1. If an item is tested and acquired ("Yes" result on column E) by the student three times and on three different but consecutive dates (ex. 1/26 - 1/27 - 1/28 or 1/26 - 1/29/ - 2/5) _
' and the testing is done by at least 2 different uIDs then the item is listed as Completed (column I).
'This section tests for the above condition.
'Specifically it checks to see if there are 3 consecutive dates and that those dates all have corresponding "acquired = YES"
'and that at least two of the dates have different uIDs
For i = 1 To n
    For j = 1 To n
        For k = 1 To n
            If i <> j And j <> k And DateArray(i) <> 0 And DateArray(j) <> 0 And DateArray(k) <> 0 _
                And DateArray(i) < DateArray(j) And DateArray(i) > DateArray(k) _
                And ((uIDArray(i) <> uIDArray(j)) Or (uIDArray(i) <> uIDArray(k)) Or _
                (uIDArray(j) <> uIDArray(k))) Then
            completed = True
            Exit Function
            End If
        Next k
    Next j
Next i

'If an item is acquired 5 times in the same day and tested the same uID, then the item is listed as Completed.
'x is a counter for occurrences on the same date
'For the counter to advance, dates must match and uID must be the same
x = 1
For i = 1 To n
    For j = 1 To n
        If i <> j And DateArray(i) = DateArray(j) And DateArray(i) <> 0 And DateArray(j) <> 0 And uIDArray(i) = uIDArray(j) Then
        x = x + 1
        DateArray(j) = 0
        End If
    Next j
Next i
'Now we set the output of the function in accordance with the counter.  If 5 or more, it is set to true.  Otherwise, false.
completed = (x >= 5)
End Function
Function datecompleted(datatable As Range, item As Range)
n = datatable.Rows.Count
Dim ItemArray()
ReDim ItemArray(1 To n)
Dim DateArray()
ReDim DateArray(1 To n)
Dim uIDArray()
ReDim uIDArray(1 To n)
Dim AcqArray()
ReDim AcqArray(1 To n)

'This section populates the VBA arrays that this formula uses to solve the problem.
'Note that it only populates dates if the item from the table matches the item requested.
For i = 1 To n
AcqArray(i) = datatable.Offset(i - 1, 4).Resize(1, 1).Value
ItemArray(i) = ((datatable.Offset(i - 1, 1).Resize(1, 1).Value = item) And (AcqArray(i) = "Yes"))
If ItemArray(i) = True Then
    DateArray(i) = datatable.Offset(i - 1, 2).Resize(1, 1).Value
    Else
    DateArray(i) = 0
End If
uIDArray(i) = datatable.Offset(i - 1, 3).Resize(1, 1).Value
Next i

'1. If an item is tested and acquired ("Yes" result on column E) by the student three times and on three different but consecutive dates (ex. 1/26 - 1/27 - 1/28 or 1/26 - 1/29/ - 2/5) _
' and the testing is done by at least 2 different uIDs then the item is listed as Completed (column I).
'This section tests for the above condition.
'Specifically it checks to see if there are 3 consecutive dates and that those dates all have corresponding "acquired = YES"
'and that at least two of the dates have different uIDs
'If all that passes, then the return value is the first, largest date satisfying the condition
For i = 1 To n
    For j = 1 To n
        For k = 1 To n
            If i <> j And j <> k And DateArray(i) <> 0 And DateArray(j) <> 0 And DateArray(k) <> 0 _
                And DateArray(i) < DateArray(j) And DateArray(i) > DateArray(k) _
                And ((uIDArray(i) <> uIDArray(j)) Or (uIDArray(i) <> uIDArray(k)) Or _
                (uIDArray(j) <> uIDArray(k))) Then
            datecompleted = DateArray(j)
            Exit Function
            End If
        Next k
    Next j
Next i

'If an item is acquired 5 times in the same day and tested the same uID, then the item is listed as Completed.
'x is a counter for occurrences on the same date
'For the counter to advance, dates must match and uID must be the same.  Each time the counter advances, it saves a tentative "answer" value
x = 1
For i = 1 To n
    For j = 1 To n
        If i <> j And DateArray(i) = DateArray(j) And DateArray(i) <> 0 And DateArray(j) <> 0 And uIDArray(i) = uIDArray(j) Then
        x = x + 1
        answer = DateArray(j)
        DateArray(j) = 0
        End If
    Next j
Next i
'Now we set the output of the function in accordance with the counter.  If 5 or more, it is set to the date.  Otherwise, blank.
If x >= 5 Then datecompleted = answer Else datecompleted = ""
End Function

The code will create two UDFs. In the cell where you want the return value, you would put =completed("source data","item name"). The second one is =datecompleted("source data","item name"). Give it a try and let me know.

 

[jmu2013]

Thank you bruderbell! I'll familiarize myself with VBA first then I will apply the code that you shared with me. I'll update you with my progress on the weekend.