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" 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 readableNext: Macro to rename Tab and then save as