+ Reply to Thread
Results 1 to 6 of 6

Preparing a from and to matrix

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    89

    Preparing a from and to matrix

    Hi All,

    I work a lot with distances in my line of work. I need to prepare a matrix of all possible combinations of from and to's.

    An example I have 4 values for from and 4 for to


    From To
    A 1
    B 2
    C 3
    D 4




    I want to get all possible combinations. Which in this case will be 16


    From To
    A 1
    A 2
    A 3
    A 4
    B 1
    B 2
    B 3
    B 4
    C 1
    C 2
    C 3
    C 4
    D 1
    D 2
    D 3
    D 4

    Just to give an overview. These points are addresses in a town or state. So I need to get the distances for each from and to combination. So I have to prepare a matrix in excel. The distances are worked out in a software outside of excel. But the matrix is required in order to tell the software which combinations are required.


    I am not sure if it will be a simple formula to write or a macro.

    Any help appreciated.

    Thank you

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Preparing a from and to matrix

    Is the attached of any use to you?

    I assume your example data isn't truly representative of your real data so you'll have to tinker with the formulas to split the data from D into E & F.


    BSB
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-22-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    89

    Re: Preparing a from and to matrix

    Hi BSB,

    Thanks for the reply.
    It does kind of help if the data is in 2 different columns.

    But if the data is in a single column, would there be any way to do it.

    Attached is an example of how the actual data looks like and the expected result.

    Cheers

    logisticsexcel

    OD_Matrix.xlsx

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Preparing a from and to matrix

    How about this version?
    You have rows where To and From are the same, but they should be easily weeded out.

    Failing that, I'm stumped.

    BSB
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-22-2014
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    89

    Re: Preparing a from and to matrix

    Thank you very much. That's exactly what I needed.

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Preparing a from and to matrix

    Happy to help

    Don't forget to mark the thread as SOLVED if you're happy you have your solution.

    BSB

+ 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. Replies: 2
    Last Post: 11-05-2014, 03:41 AM
  2. Eigenvector of matrix for nxn matrix of variable size
    By rocketscientist165 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2014, 10:51 AM
  3. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  4. [SOLVED] need help in preparing a checklist
    By sbehera in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2013, 02:34 PM
  5. Using a transition matrix as input to a cummulative matrix
    By Walter12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 04:19 AM
  6. Preparing for Interview would like some help
    By coreyjohnson711 in forum Excel General
    Replies: 0
    Last Post: 06-27-2012, 11:01 PM
  7. preparing for an ascii file
    By tankgirl215 in forum Excel General
    Replies: 7
    Last Post: 06-12-2006, 05:55 PM

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