Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: formula to generate reordring or resorting from given word

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default formula to generate reordring or resorting from given word

    hello
    need formula to generate reordring or resorting from given word
    example i have this 3 word or lets say more
    a1=cat b1=dog c1=bee
    now i need formula to reorder them on max possible combination to be generated in bellow rows
    and formula work with more words and mean generate max combination
    cat bee dog

    dog cat bee

    dog bee cat

    bee dog cat

    bee cat dog

    thanks

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    you are talking combinations SO DOG CAT BEE IS THE SAME AS DOG BEE CAT be clear if you want permutations or combinations.........

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    Quote Originally Posted by oldbrewer View Post
    you are talking combinations SO DOG CAT BEE IS THE SAME AS DOG BEE CAT be clear if you want permutations or combinations.........
    yes permutation correct plus i am on excell 2010

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    bear - bear - bear - bear 4 snake 1 bear
    bear - bear - bear - dog 4 snake 2 dog
    bear - bear - bear - cat 4 snake 3 cat
    bear - bear - bear - snake 4 snake 4 snake
    bear - bear - dog - bear
    bear - bear - dog - dog
    bear - bear - dog - cat snake - snake - snake - snake
    bear - bear - dog - snake
    bear - bear - cat - bear
    bear - bear - cat - dog
    bear - bear - cat - cat this macro does it
    bear - bear - cat - snake
    bear - bear - snake - bear Sub Macro4()
    bear - bear - snake - dog '
    bear - bear - snake - cat ' Macro4 Macro
    bear - bear - snake - snake ' Macro recorded 18/03/2019 by bob
    bear - dog - bear - bear '
    bear - dog - bear - dog
    bear - dog - bear - cat '
    bear - dog - bear - snake For j = 1 To 4
    bear - dog - dog - bear For k = 1 To 4
    bear - dog - dog - dog For l = 1 To 4
    bear - dog - dog - cat For m = 1 To 4
    bear - dog - dog - snake Cells(1, 6) = j: Cells(2, 6) = k: Cells(3, 6) = l: Cells(4, 6) = m
    bear - dog - cat - bear thisperm = Cells(7, 7)
    bear - dog - cat - dog Sum = Sum + 1
    bear - dog - cat - cat Cells(Sum, 1) = thisperm
    bear - dog - cat - snake Next m
    bear - dog - snake - bear Next l
    bear - dog - snake - dog Next k
    bear - dog - snake - cat Next j
    bear - dog - snake - snake End Sub
    bear - cat - bear - bear
    bear - cat - bear - dog
    bear - cat - bear - cat
    bear - cat - bear - snake
    bear - cat - dog - bear
    bear - cat - dog - dog
    bear - cat - dog - cat
    bear - cat - dog - snake
    bear - cat - cat - bear
    bear - cat - cat - dog
    bear - cat - cat - cat
    bear - cat - cat - snake
    bear - cat - snake - bear
    bear - cat - snake - dog
    bear - cat - snake - cat
    bear - cat - snake - snake
    bear - snake - bear - bear
    bear - snake - bear - dog
    bear - snake - bear - cat
    bear - snake - bear - snake
    bear - snake - dog - bear
    bear - snake - dog - dog
    bear - snake - dog - cat
    bear - snake - dog - snake
    bear - snake - cat - bear
    bear - snake - cat - dog
    bear - snake - cat - cat
    bear - snake - cat - snake
    bear - snake - snake - bear
    bear - snake - snake - dog
    bear - snake - snake - cat
    bear - snake - snake - snake
    dog - bear - bear - bear
    dog - bear - bear - dog
    dog - bear - bear - cat
    dog - bear - bear - snake
    dog - bear - dog - bear
    dog - bear - dog - dog
    dog - bear - dog - cat
    dog - bear - dog - snake
    dog - bear - cat - bear
    dog - bear - cat - dog
    dog - bear - cat - cat
    dog - bear - cat - snake
    dog - bear - snake - bear
    dog - bear - snake - dog
    dog - bear - snake - cat
    dog - bear - snake - snake
    dog - dog - bear - bear
    dog - dog - bear - dog
    dog - dog - bear - cat
    dog - dog - bear - snake
    dog - dog - dog - bear
    dog - dog - dog - dog
    dog - dog - dog - cat
    dog - dog - dog - snake
    dog - dog - cat - bear
    dog - dog - cat - dog
    dog - dog - cat - cat
    dog - dog - cat - snake
    dog - dog - snake - bear
    dog - dog - snake - dog
    dog - dog - snake - cat
    dog - dog - snake - snake
    dog - cat - bear - bear
    dog - cat - bear - dog
    dog - cat - bear - cat
    dog - cat - bear - snake
    dog - cat - dog - bear
    dog - cat - dog - dog
    dog - cat - dog - cat
    dog - cat - dog - snake
    dog - cat - cat - bear
    dog - cat - cat - dog
    dog - cat - cat - cat
    dog - cat - cat - snake
    dog - cat - snake - bear
    dog - cat - snake - dog
    dog - cat - snake - cat
    dog - cat - snake - snake
    dog - snake - bear - bear
    dog - snake - bear - dog
    dog - snake - bear - cat
    dog - snake - bear - snake
    dog - snake - dog - bear
    dog - snake - dog - dog
    dog - snake - dog - cat
    dog - snake - dog - snake
    dog - snake - cat - bear
    dog - snake - cat - dog
    dog - snake - cat - cat
    dog - snake - cat - snake
    dog - snake - snake - bear
    dog - snake - snake - dog
    dog - snake - snake - cat
    dog - snake - snake - snake
    cat - bear - bear - bear
    cat - bear - bear - dog
    cat - bear - bear - cat
    cat - bear - bear - snake
    cat - bear - dog - bear
    cat - bear - dog - dog
    cat - bear - dog - cat
    cat - bear - dog - snake
    cat - bear - cat - bear
    cat - bear - cat - dog
    cat - bear - cat - cat
    cat - bear - cat - snake
    cat - bear - snake - bear
    cat - bear - snake - dog
    cat - bear - snake - cat
    cat - bear - snake - snake
    cat - dog - bear - bear
    cat - dog - bear - dog
    cat - dog - bear - cat
    cat - dog - bear - snake
    cat - dog - dog - bear
    cat - dog - dog - dog
    cat - dog - dog - cat
    cat - dog - dog - snake
    cat - dog - cat - bear
    cat - dog - cat - dog
    cat - dog - cat - cat
    cat - dog - cat - snake
    cat - dog - snake - bear
    cat - dog - snake - dog
    cat - dog - snake - cat
    cat - dog - snake - snake
    cat - cat - bear - bear
    cat - cat - bear - dog
    cat - cat - bear - cat
    cat - cat - bear - snake
    cat - cat - dog - bear
    cat - cat - dog - dog
    cat - cat - dog - cat
    cat - cat - dog - snake
    cat - cat - cat - bear
    cat - cat - cat - dog
    cat - cat - cat - cat
    cat - cat - cat - snake
    cat - cat - snake - bear
    cat - cat - snake - dog
    cat - cat - snake - cat
    cat - cat - snake - snake
    cat - snake - bear - bear
    cat - snake - bear - dog
    cat - snake - bear - cat
    cat - snake - bear - snake
    cat - snake - dog - bear
    cat - snake - dog - dog
    cat - snake - dog - cat
    cat - snake - dog - snake
    cat - snake - cat - bear
    cat - snake - cat - dog
    cat - snake - cat - cat
    cat - snake - cat - snake
    cat - snake - snake - bear
    cat - snake - snake - dog
    cat - snake - snake - cat
    cat - snake - snake - snake
    snake - bear - bear - bear
    snake - bear - bear - dog
    snake - bear - bear - cat
    snake - bear - bear - snake
    snake - bear - dog - bear
    snake - bear - dog - dog
    snake - bear - dog - cat
    snake - bear - dog - snake
    snake - bear - cat - bear
    snake - bear - cat - dog
    snake - bear - cat - cat
    snake - bear - cat - snake
    snake - bear - snake - bear
    snake - bear - snake - dog
    snake - bear - snake - cat
    snake - bear - snake - snake
    snake - dog - bear - bear
    snake - dog - bear - dog
    snake - dog - bear - cat
    snake - dog - bear - snake
    snake - dog - dog - bear
    snake - dog - dog - dog
    snake - dog - dog - cat
    snake - dog - dog - snake
    snake - dog - cat - bear
    snake - dog - cat - dog
    snake - dog - cat - cat
    snake - dog - cat - snake
    snake - dog - snake - bear
    snake - dog - snake - dog
    snake - dog - snake - cat
    snake - dog - snake - snake
    snake - cat - bear - bear
    snake - cat - bear - dog
    snake - cat - bear - cat
    snake - cat - bear - snake
    snake - cat - dog - bear
    snake - cat - dog - dog
    snake - cat - dog - cat
    snake - cat - dog - snake
    snake - cat - cat - bear
    snake - cat - cat - dog
    snake - cat - cat - cat
    snake - cat - cat - snake
    snake - cat - snake - bear
    snake - cat - snake - dog
    snake - cat - snake - cat
    snake - cat - snake - snake
    snake - snake - bear - bear
    snake - snake - bear - dog
    snake - snake - bear - cat
    snake - snake - bear - snake
    snake - snake - dog - bear
    snake - snake - dog - dog
    snake - snake - dog - cat
    snake - snake - dog - snake
    snake - snake - cat - bear
    snake - snake - cat - dog
    snake - snake - cat - cat
    snake - snake - cat - snake
    snake - snake - snake - bear
    snake - snake - snake - dog
    snake - snake - snake - cat
    snake - snake - snake - snake

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    thanks how use it i added macro and run it it generate number In f1 f2 f3 f4
    i added
    A1 bear
    A2 dog
    A3 cat
    A4 snake

    Code:
    Sub Macro4() 
                '                             
                ' Macro4 Macro                         
                     ' Macro recorded 18/03/2019 by bob                 
            '                             
                                    
        '                             
                For j = 1 To 4                         
        For k = 1 To 4                         
    For l = 1 To 4                         
        For m = 1 To 4                         
            Cells(1, 6) = j: Cells(2, 6) = k: Cells(3, 6) = l: Cells(4, 6) = m         
        thisperm = Cells(7, 7)                     
        Sum = Sum + 1                         
        Cells(Sum, 1) = thisperm                     
            Next m                             
                Next l                             
            Next k                             
            Next j                             
                    End Sub

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    bear - bear - bear - bear 4 snake 1 bear
    bear - bear - bear - dog 4 snake 2 dog
    bear - bear - bear - cat 4 snake 3 cat
    bear - bear - bear - snake 4 snake 4 snake
    bear - bear - dog - bear col F COL g I1:J4 is named mytable
    bear - bear - dog - dog
    bear - bear - dog - cat snake - snake - snake - snake
    bear - bear - dog - snake
    bear - bear - cat - bear
    bear - bear - cat - dog
    bear - bear - cat - cat this macro does it
    bear - bear - cat - snake
    bear - bear - snake - bear Sub Macro4()
    bear - bear - snake - dog '
    bear - bear - snake - cat ' Macro4 Macro
    bear - bear - snake - snake ' Macro recorded 18/03/2019 by bob
    bear - dog - bear - bear '
    bear - dog - bear - dog
    bear - dog - bear - cat '
    bear - dog - bear - snake For j = 1 To 4
    bear - dog - dog - bear For k = 1 To 4
    bear - dog - dog - dog For l = 1 To 4
    bear - dog - dog - cat For m = 1 To 4
    bear - dog - dog - snake Cells(1, 6) = j: Cells(2, 6) = k: Cells(3, 6) = l: Cells(4, 6) = m
    bear - dog - cat - bear thisperm = Cells(7, 7)
    bear - dog - cat - dog Sum = Sum + 1
    bear - dog - cat - cat Cells(Sum, 1) = thisperm
    bear - dog - cat - snake Next m
    bear - dog - snake - bear Next l
    bear - dog - snake - dog Next k
    bear - dog - snake - cat Next j
    bear - dog - snake - snake End Sub
    bear - cat - bear - bear
    bear - cat - bear - dog
    bear - cat - bear - cat the macro puts every permutation of 1,2,3,4 in col F (F1:F4)
    bear - cat - bear - snake
    bear - cat - dog - bear in G1:G4 there is a vlookup formula
    bear - cat - dog - dog
    bear - cat - dog - cat eg in G1
    bear - cat - dog - snake .=VLOOKUP(F1,mytable,2)
    bear - cat - cat - bear
    bear - cat - cat - dog
    bear - cat - cat - cat cell G7 concatenates G1,G2,G3,G4
    bear - cat - cat - snake
    bear - cat - snake - bear
    bear - cat - snake - dog

  7. #7
    New Member
    Join Date
    Mar 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    can you please send me your exell file?
    not sure how to use above code it just genereate number i am ver new in excell

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    It is not permitted in this forum to Email files.

    the first 4 lines of code generate a unique permutation

    it then sets F1 equal to J F2 to K etc and cell s G next to them is the result of the vlookup

    thisperm is set to equal cells(7,7) which is G7

    each unique permutation of animal sets is printed in column A

    what don't you understand ?

  9. #9
    New Member
    Join Date
    Mar 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    i dont understand why yours generate sequence word,mine not
    plus count me zero knowledge excell
    i know about how work with it a little bit long tome ago now when i want using stack in it
    if possible make screen shot from every step of yours

    much appreciated

  10. #10
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,985
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula to generate reordring or resorting from givin word

    make sure "your" cells are exactly where mine are

    F1 TO F4 IS WHERE THE MACRO PLACES THE NUMBERS
    G1 TO G4 USES THE VALUES IN F TO RETURN "ANIMAL NAME" FROM MYTABLE
    G7 IS THE 4 ANIMAL NAMES CONCATENATED

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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