# Thread: formula to generate reordring or resorting from given word Thanks:  8 Post #5244630 (1)Post #5246161 (1)Post #5244679 (1)Post #5246231 (1)Post #5244301 (1) Likes:  6 Post #5244301 (1)Post #5246231 (1)Post #5244679 (1)Post #5246521 (1)Post #5244876 (1)

1. ## 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. ## 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. ## Re: formula to generate reordring or resorting from givin word

Originally Posted by oldbrewer
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. ## 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. ## 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
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. ## 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. ## Re: formula to generate reordring or resorting from givin word

not sure how to use above code it just genereate number i am ver new in excell

8. ## 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. ## 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. ## 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