Prev: Try this - hopefully now readable
Next: Macro to rename Tab and then save as
From: Steve on 24 May 2010 10:19
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
From: Per Jessen on 25 May 2010 03:45
Hi Steve

With your data in column A:D, 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

"Steve" <dafella007(a)yahoo.com> skrev i meddelelsen
news:74668e2c-e034-4269-be03-088e030dcf01(a)o12g2000vba.googlegroups.com...
> 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

 | 
Pages: 1
Prev: Try this - hopefully now readable
Next: Macro to rename Tab and then save as