# Object Assignments based on coordinates

• ### Question

• I wish to assign an object (a, b, c etc ) to a person (1 or 2 or 3 etc) based on the following (see image)

1. The lat/long of the object must be equal (=) to or between +/- 0.05 the lat/long of the person.

2. The Max/Min wait days for the object to be assigned to can between 27 to 34 days (excluding <g class="gr_ gr_51 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="51" id="51">sundays</g>

3.<g class="gr_ gr_119 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="119" id="119">The</g> total and objects to assign to a person <g class="gr_ gr_59 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="59" id="59">is</g> 5. When this condition is met then assign to the next available person who meets the criteria of number 2

Is there a way of doing this in excel, I would appreciate help with this formula

 Object Last day person had the object lat long Min wait days Max wait days Person Lat Long Object assigned to person? a 27 17.33333 -20.77777 27 34 1 17.77777 -20.33333 b 28 17.44444 -20.66666 27 34 2 18.22221 -19.88889 c 28 17.55555 -20.55555 27 34 3 18.55554 -19.55556 d 30 17.66666 -20.44444 27 34 e 32 17.77777 -20.33333 27 34 f 33 17.88888 -20.22222 27 34 g 34 17.99999 -20.11111 27 34 h 27 18.1111 -20 27 34 i 28 18.22221 -19.88889 27 34 j 28 18.33332 -19.77778 27 34 k 30 18.44443 -19.66667 27 34 l 32 18.55554 -19.55556 27 34 m 33 18.66665 -19.44445 27 34 n 34 18.77776 -19.33334 27 34 o 28 18.88887 -19.22223 27 34

• Edited by Monday, July 16, 2018 1:24 AM
Monday, July 16, 2018 1:19 AM

### All replies

• Hello Lasonico,

You could try to get the lat and long and then use them to filter source data. And then you could loop through the filtered data to get the object.

Here is the simple code.

```Sub Macro1()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Set sourceSheet = Worksheets("Sheet2")
Set targetSheet = Worksheets("Sheet3")
TargetSheetLastRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row
For i = 2 To TargetSheetLastRow
FilterLat = targetSheet.Cells(i, 2)
FilterLong = targetSheet.Cells(i, 3)
'filter between +/- 0.05
sourceSheet.Columns("A:F").AutoFilter Field:=3, Criteria1:=">=" & (FilterLat - 0.05) _
, Operator:=xlAnd, Criteria2:="<=" & (FilterLat + 0.05)
sourceSheet.Columns("A:F").AutoFilter Field:=4, Criteria1:=">=" & (FilterLong - 0.05) _
, Operator:=xlAnd, Criteria2:="<=" & (FilterLong + 0.05)
sourceSheet.Columns("A:F").AutoFilter Field:=5, Criteria1:=">=27" _
, Operator:=xlAnd, Criteria2:="<=34"
sourceSheet.Columns("A:F").AutoFilter Field:=6, Criteria1:=">=27" _
, Operator:=xlAnd, Criteria2:="<=34"
ObjectStr = ""
ObjectCount = 0
For Each rw In sourceSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows
If rw.Row <> 1 And ObjectCount < 5 Then
ObjectStr = ObjectStr & "," & rw.Cells(1, 1)
ObjectCount = ObjectCount + 1
End If
Next rw
If Len(ObjectStr) > 0 Then ObjectStr = Right(ObjectStr, Len(ObjectStr) - 1)
targetSheet.Cells(i, 4) = ObjectStr
Next i
sourceSheet.AutoFilterMode = False
End Sub
```

Best Regards,

Terry

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Monday, July 16, 2018 6:42 AM