none
Object Assignments based on coordinates RRS feed

  • 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 Lasonico 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