none
Source Destination mapping RRS feed

  • Question

  • I have different source destination combinations, and 2 parameters A & B, on which I need to decide preference(rank 1 to 12) for the Source to serve the destination. Every source serves around 20k destinations and there are multiple service providers for the service having different values of the parameters.We have these workbooks from the service providers in following format

    Source   Destination   SLA   Cost

    We got to select the vendors with minimum on parameter "A" and then look for minimum on parameter B, and even if the rank is not decided, a static input stating preference will be provided. Would like to keep the sheet dynamic

    Basis this, I want to prepare an output in following format

                                                                                Source

             Destination                    1     2     3     4    5    6    7    8    9    10   11    12

                    D1                         S1   S2    S3   S4  S5 and so on 

                    D2                         S1   S2    S3   S4  S5 and so on

                    D3

     Can you please help me with the working

    Thursday, May 10, 2018 6:25 AM

All replies

  • It seems possible with VBA. Pls share some sample workbook in onedrive and share link.

    Best Regards, Asadulla Javed

    Thursday, May 10, 2018 11:58 AM
    Answerer
  • https://1drv.ms/x/s!AriTq3cnRg3khBIKakmQOVrWETKI

    i HAVE COLLATED THE DATA ON SINGLE WORKSHEET. Please find the same

    Friday, May 11, 2018 5:37 AM
  • Hello RoshanPatil,

    So you want to list all the destinations(110001,110002....) in the first column and then sort source(A,B,C...) with the same destination by SLA?

    I thought D1 is 110001 and then try to sort value and failed to understand the order of ACDB. Did I fully misunderstand your design or you did not order it in the simply file?

    Besides, in the simply file, all source in same row have the same destination, will sources have same destination be assigned in different rows in your actually file?

    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.

    Friday, May 11, 2018 6:34 AM
  • So you want to list all the destinations(110001,110002....) in the first column and then sort source(A,B,C...) with the same destination by SLA?___Yes, by SLA and cost, SLA being the primary parameter.

    I thought D1 is 110001 and then try to sort value and failed to understand the order of ACDB. Did I fully misunderstand your design or you did not order it in the simply file?___this was just a tentative entry to show the expected output. It will be the source following the logic stated earlier.  I have updated the sheet and solved for the first three destination.

    Besides, in the simply file, all source in same row have the same destination, will sources have same destination be assigned in different rows in your actually file?_____Actually these sources(inputs are on different worksheets. I have placed them side by side for simplicity. I don't mind copy-pasting them one below the other. Hope this helps. Also as all destinations are not served by every source, the rows will not be same for all.

    I have also added a ranking array, which needs to be used if the rank can not be uniquely derived even from SLA as well as cost

    Friday, May 11, 2018 7:13 AM
  • Hello RoshanPatil,

    I would suggest you put all the data together and then use excel sort function to sort these data. 

    Based on your shared source data, I tried below code and get a result table like below. Please try to adjust it for your need if it is helpful for you.

    Destination 1 2 3 4
    110001 A D C B
    110002 A D B C
    110003 B D A C
    110004 C B A D
    110005 B A D C
    110006 B A D C
    110007 B A D C
    110008 B D C A
    110009 A D C B
    110010 D A C B
    110011 B A D C
    110012 A D C B
    110013 D C B A
    110014 C A D B
    110015 B D C A
    110016 A D C B
    110017 B D C A

    Sub Test()
    Dim objSourceSheet As Worksheet
    Dim objTempSheet As Worksheet
    Dim objResultSheet As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set objSourceSheet = ThisWorkbook.Worksheets("Sheet1")
    Set objTempSheet = ThisWorkbook.Worksheets.Add
    Set objResultSheet = ThisWorkbook.Worksheets.Add
    objTempSheet.Name = "TempSheet"
    objResultSheet.Name = "ResultSheet"
    'Set objTempSheet = ThisWorkbook.Worksheets("TempSheet")
    'Set objResultSheet = ThisWorkbook.Worksheets("ResultSheet")
    
    objSourceSheet.Range("A1:E18").Copy objTempSheet.Range("A1")
    objSourceSheet.Range("G2:J18").Copy objTempSheet.Range("A19")
    objSourceSheet.Range("M2:P18").Copy objTempSheet.Range("A36")
    objSourceSheet.Range("S2:V18").Copy objTempSheet.Range("A53")
    objTempSheet.Range("E1") = "Rank"
    
    objTempSheet.Range("E2").Formula = "=VLOOKUP(A2,Sheet1!S$25:T$36,2)"
    objTempSheet.Range("E2").AutoFill objTempSheet.Range("E2:E69")
    
    objTempSheet.Sort.SortFields.Clear
        objTempSheet.Sort.SortFields.Add Key:=objTempSheet.Columns(2) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        objTempSheet.Sort.SortFields.Add Key:=objTempSheet.Columns(3) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        objTempSheet.Sort.SortFields.Add Key:=objTempSheet.Columns(4) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        objTempSheet.Sort.SortFields.Add Key:=objTempSheet.Columns(5) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With objTempSheet.Sort
            .SetRange objTempSheet.UsedRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    objResultSheet.Cells(1, 1) = "Destination"
    objResultSheet.Cells(1, 2) = "1"
    objResultSheet.Cells(1, 3) = "2"
    objResultSheet.Cells(1, 4) = "3"
    objResultSheet.Cells(1, 5) = "4"
    For i = 2 To 18
        Destination = objSourceSheet.Cells(i, 2)
        objTempSheet.Range("A1:E69").AutoFilter 2, Destination
        j = 1
        objResultSheet.Cells(i, 1) = Destination
        For Each cel In objTempSheet.Range("A2:A69").SpecialCells(xlCellTypeVisible)
        j = j + 1
        objResultSheet.Cells(i, j) = cel
        Next cel
    Next i
    objTempSheet.Delete
    objResultSheet.Activate
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    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, May 14, 2018 10:04 AM
  • Hello RoshanPatil,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    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.

    Wednesday, May 23, 2018 8:16 AM