+ Reply to Thread
Results 1 to 2 of 2

Excel Table reorganization

  1. #1
    Registered User
    Join Date
    10-07-2011
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    Excel 2007
    Posts
    1

    Unhappy Excel Table reorganization

    Hi experts,

    I have a table in excel where i have three columns named "First","Mid","End". Each column is filled with data like in the table below

    First Mid End
    2 18 3
    3 7 8
    3 11 9
    8 15 4
    9 19 14
    14 21 20
    14 25 4
    20 28 4

    what I want to do is to create numerical paths using the above table using the random number (example let’s say I selected 2) so starting from 2, I will pick the first three element from the same row which is = 2 18 3 then I will take the last element from this path (3) and look for the same number in the first column on the table and continue the path 2, 18,3,7,8 (omitting the repetition of 3). Likewise I want to list down all the possible paths that I can have using the above table.
    Example of a result for the above table is

    2,18,3,7,8,15,4
    2,18,3,11,9,19,14,21,20,28,4
    2,18,3,11,9,19,14,25,4

    Can someone please help me on the coding or formula that could help me get the result displayed above? Please note the number of rows contained in the table could vary. But for every row of data all three columns will have data in that row.

    Your expertise on this matter will be greatly appreciated.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Excel Table reorganization

    Please look at the attached. Enter your starting number into the yellow cell and hit go.

    The code is below.

    Please Login or Register  to view this content.
    It works by keeping track of whether new combinations are being created for each cycle and removes any duplication as it goes along.
    Attached Files Attached Files
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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