Hello everyone,
This is my first post, so I hope it will work out.
I am working on a model that is dealing with the Vehicle Routing Problem and I need to apply the Clarke and Wright savings method in Excel.
The idea is that I get a matrix that is 100x100 (for example) and I find a value in this matrix that has certain characteristics, for example the highest number.
Given that I have found the number I am looking for, I want to create a formula which tells me where the element that I found is.
So it can be that the matrix value is 22, which I filtered out based on the criteria that I set.
In this case, I want to find in my 100x100 matrix where this number 22 came from. For example from T56. In this case I want it to return the value of the header of the column and row, lets say that the columns are also numbered from 1,2,3,4,.... So the output would then be that the row number is 56 and the column number is 20, since T=20 from alphabet.
I was working with the address function, and also with Match and Index, but I couldn't make it work because in any case, I do not know where the value is, in which row or column.
The problem is also that the size of the matrix can differ at any moment because a different amount of customers can be there per day.
So i guess it has to work in VBA maybe with a for loop, but I do not know how to model it.
Thanks!
Bookmarks