+ Reply to Thread
Results 1 to 8 of 8

Returning Matrix Indices from value

  1. #1
    Registered User
    Join Date
    04-21-2019
    Location
    The Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Returning Matrix Indices from value

    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!

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Returning Matrix Indices from value

    Hi maicoooooo

    Welcome to the forum...

    https://www.excelforum.com/excel-pro...ml#post5103696

    Ideally, you can upload a sample file which the members can make use of...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Returning Matrix Indices from value

    Hi there,

    See if the attached workbook gets you moving in the right direction.

    It uses the following code in the VBA CodeModule of the worksheet which contains the data (i.e. not in a standard VBA CodeModule):

    Please Login or Register  to view this content.

    Entering a value in the blue-shaded cell will (if that value exists in the matrix) display the Row and column coordinates of the located cell with reference to the worksheet and also with reference to the matrix.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-21-2019
    Location
    The Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Returning Matrix Indices from value

    Hello Greg M!

    Thank for your reply, it was helpful to see how you were making this and it seems to be doing exactly what I asked for. However, I think I didnt formulate well enough on what it is that I need, so I'm sorry for that.

    The thing is that I have x customers and this amount of x changes everyday. The matrix consists of x*x values, with in there the distances between customer 1 and 2, called Xij with the values 1 and 2 for i and j and so on, also distances between customer 76 and customer 89, and all. This means that there is only half of a matrix (because bottom left is symmatrical with top right. Because distance Xij = Xji in this case.
    So the point is that I have automatically created a function that recovers all n numbers from the matrix (that was only filled half) and I have sorted them automatically from high to low. So let's say there are 15 customers, then the matrix consists of ((0.5*15*15) -the amount of distances from i to i (Xii) because this distance equals 0)
    All these n numbers are sorted automatically in order from high to low, and from all these n numbers, the function must automatically say where the numbers come from exactly, using only the table position formula as you created before already for me. The main difficulty for me is that the amount of customers can change per day and so in that way I do not know how to create this automatically for all n numbers.

    Would be great if you could help me out, I really appreciate it!

    Maico

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Returning Matrix Indices from value

    Hi again,

    Many thanks for your feedback and also for the detailed way in which you explained your requirements - that makes life SO much easier for contributors!!!


    Have a look at the attached workbook and see if it's going in the right direction.

    I've listed your customers (grid axes) as A, B . . . Z, and the distances between customers (grid values) as AB, AC . . . YZ. Obviously your own grid values will be actual distances, but using AB, AC etc. might make it a bit easier to follow how the process works.

    The grid will cater for up to 26 customers, and entering a value (2 - 26) in the blue-shaded cell will cause the grid to display data for the appropriate number of customers.

    Clicking on the "Update Co-ordinates" button will update a table in Columns A:C. This table will contain a row for each distance value in the grid, and each such row will contain the distance value (e.g. AB) together with the row and column numbers appropriate to the position of that distance value within the grid.


    The code used is as follows:

    Please Login or Register  to view this content.
    The highlighted value may be altered to suit your own requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-21-2019
    Location
    The Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Returning Matrix Indices from value

    Hello Greg M!

    I didn't find the time yet to fully look into what you were sending me as an answer, since I ended up finding another problem that I had to solve first. So thanks for you answer, but I cannot see yet if it will be applicable, although it seemed good.

    My problem is that in my Vehicle Routing Problem system, I have build a model that is quite similar to this page. It is on Russian, so you will most likely not be able to translate the words, but the table should be self-explanatory.
    I have a problem with adding an attachment, so finding my problem can be done on the Russian website of infostart, with number 443585, where I am talking about table 4, row number 9

    In column 12(yellow row shows columns), there is the route of which customers I have to visit. Based on certain characteristics, it calculated that one route is supposed to be 0 5 8 3 12 0, and another route is 0 1 11 0. The routes are of different lengths, because in all routes the capacity of the vehicle cannot be exceeded.
    When you would look at the row that starts with number 9, it has to be checked if customers 11 and 5 were already used in routes before via column 12, which is made with concatenation)
    The values in column 8 and 9 give the corresponding capacities of the vehicles for the routes that these numbers are already in. If the customer number (column 2 and 3) is not used before in any route, the value in table 1 is added instead.

    This means that in this row starting with number 9, the value in column 8 (925) is found because customer 11 is already in route 0 1 11 0, and the capacity up to that point = 925.
    For the value in column 9, the number 1300 is found, since customer 5 can already be found in route 0 5 8 3 12 0.

    I have to create a formula for the values in column 8 and 9. This formula in this case needs to do the following:
    Check if the customer number in column 2/3 (I*/J*) has already been used in any routes before (column 12), and find the latest occurences of this happening (I do not want the number 600 for customer 8, because later already a bigger route was found with 8 included, which had capacity 1300)
    If this customer number in column 2/3 has not been used before, take the value from table 1 (although this is not a problem)

    The problem is that I do not know how to make a formula that finds the latest occurence of a value in column 12, and then to fill in the corresponding capacity from up to that point.
    The number of blank rows in between (which mean that some constraints were not met) can change every time and is not set. So I need to create a formula that helps me (in the row starting with 9) find in column 8 and 9 the values 1300 and 925.

    Thanks for your help already up to this moment. Maybe my question is not anymore really in the correct thread, but maybe you can point me a bit in the right direction or already fix it for me.

    Thanks!

    Maico

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,328

    Re: Returning Matrix Indices from value

    Your duplicate has been closed and this thread has been moved to the Functions & Formulas section.

    Attach your workbook here.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    04-21-2019
    Location
    The Netherlands
    MS-Off Ver
    2013
    Posts
    12

    Re: Returning Matrix Indices from value

    In the attachment, my file so far can be found. Thank you for your remarks and for moving it to a different topic.

    I will make an additional explanation because I will now only talk about my excel file.

    I am working in sheet 2, in the third table, starting at row 28.
    I tried to copy the work from the earlier mentioned website but this is what I have for now.

    Columns B and C (in this table called i and j) represent two customers that should be connected on a certain route to save time or costs, while the capacity of the truck can not be exceeded.
    Row number 32 therefore combines customer 6 and 8, which have a demand of 70 and 90 (found in column q1 and q2). These will be combined, because the constraints are all met, columns with plusses.

    What I now have to make, is a formula in column M and N, q1 and q2.
    This formula should see which customers need to be combined.
    A good example can be found in row number 64. Customer 1 and 4 need to be combined. Customer 4 has already been used before in a route (column S), where the current longest route including a 4 can be found in cell S36.
    The current capacity of that route so far can be found in cell Y36 (400).
    Since the capacity is only 400, something can be added to make sure that the capacity of 500 can not be exceeded, which is why in cell N64, i would like to see the value 400, which is the used capacity of a truck for the longest existing route that the customer in column B or C can already be found in.

    So Cell N64 should return value 400 by checking in column S where the longest route can be found that already includes the customer 4.
    Cell M64 should return a value from a table at the top of the sheet, starting at cell N1, since customer 1 didn't happen before in any route.

    I can do that last part, saying that I want the value from the top table if it did not occur in any route yet.
    The problem is that I cannot make a formula that is constantly checking in column S if the number of the customer already occured, and connect it with the capacity used up to that moment.

    Sorry for the unclear excel file. I was trying a lot, but the concatenation in cell S still seems for me the best way to give an output of which routes thusfar have been created.

    When the capacity of a truck is exceeded, a new route should start, with capacity 0 ofcourse, since you're starting again with an empty truck.
    This should also be added to the file. The problem is that in this case the number of blank cells in column S can vary, since that all depends on the constraints, if they are met or not. So a new route can be started if the previous was too full already to combine one more customer that has a large demand, but the previous route should be able to be extended once a customer can be added that has a small capacity.

    Some feedback and help would be deeply valued.
    Thanks already!

    Regards, Maico
    P.S. I hope it works with attaching the excel file
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Returning Matrix Indices from value
    By Maicoooooo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-21-2019, 10:46 AM
  2. [SOLVED] Index Matrix not returning correct value
    By JasXel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-08-2018, 06:59 AM
  3. [SOLVED] Returning a value in a matrix
    By Stuck14 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 11:16 AM
  4. #DIV/0! on indices help
    By divedeeper in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2013, 10:29 AM
  5. Returning the diagonal of a matrix as a column vector
    By thebski in forum Excel General
    Replies: 1
    Last Post: 06-04-2012, 03:45 PM
  6. Returning the value from another matrix sheet
    By dilse in forum Excel General
    Replies: 3
    Last Post: 06-02-2011, 03:01 AM
  7. Get row indices where...
    By coa01gsb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-10-2007, 11:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1