Need Suggestions - Find < 30 days & Mult-Location entries

[Steve]

I’m trying to come up with an approach to mark applicants who have
applied with 30 days or have applied at other locations.

I have about 5000 rows
Column =
Name
Location
Checkin Date
Checkout Date
Within30
MultiLoc

1. I need to search all records and find where name is duplicated or
more than once.
Then
For each row of the name listed more than once compare the first
checkout date to the next checkin date.
If value is equal to or less than 30
Place a ‘X’ in the Within30 column for all rows of that name else
leave blank

2. I need to search all records and find where name is duplicated or
more than once.
Then
For each row of the name listed more than once compare the Location
If either of the locations are different then
Place a X in the MultiLoc column for all rows of that name else leave
blank

Any thoughts on how to tackle this?


Thanks
Steve

 

[Per Jessen]

Hi Steve

With your data in column A, you can use this formula to calculate
MultiLoc:

=IF(SUMPRODUCT(--($A$2:$A$6000=A2),--($B$2:$B$6000<>B2))>0,"X","")

To calculate WithIn30, I have created this UDF, which is to be inserted into
a general module:

Function WithIn30(Name As Range, NameRng As Range, CheckIn As Range,
CheckOut As Range) As String
Application.Volatile
Dim NameCount As Long
Dim ChkOut As Long
Dim ChkIn As Long
Dim f As Range
NameCount = Application.WorksheetFunction.CountIf(NameRng, Name)
If NameCount = 1 Then
WithIn30 = ""
Exit Function
End If
For n = 1 To NameCount
For r = Name.Row + 1 To NameRng.Rows.Count - 1
Debug.Print r
If Cells(r, Name.Column) = Name Then
ChkOut = Cells(Name.Row, CheckOut.Column)
ChkIn = Cells(r, CheckIn.Column)
If ChkIn - ChkOut <= 30 Then
WithIn30 = "X"
Exit For
End If
Set Name = Cells(r, Name.Column)
End If
Next
Next
End Function

Now use this formula in WithIn30 Column

=within30(A2,$A$2:$A$6000,$C$2:$C$6000,$D$2:$D$6000)

Regards,
Per