formula to generate reordring or resorting from given word

newbexcell

New Member
Joined
Mar 17, 2019
Messages
12
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.........
 
Upvote 0
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.........
yes permutation:) correct plus i am on excell 2010
 
Upvote 0
Re: formula to generate reordring or resorting from givin word

bear - bear - bear - bear4snake1bear
bear - bear - bear - dog4snake2dog
bear - bear - bear - cat4snake3cat
bear - bear - bear - snake4snake4snake
bear - bear - dog - bear
bear - bear - dog - dog
bear - bear - dog - catsnake - snake - snake - snake
bear - bear - dog - snake
bear - bear - cat - bear
bear - bear - cat - dog
bear - bear - cat - catthis macro does it
bear - bear - cat - snake
bear - bear - snake - bearSub 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 - snakeEnd 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

<colgroup><col span="14"></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
Re: formula to generate reordring or resorting from givin word

bear - bear - bear - bear4snake1bear
bear - bear - bear - dog4snake2dog
bear - bear - bear - cat4snake3cat
bear - bear - bear - snake4snake4snake
bear - bear - dog - bearcol FCOL gI1:J4 is named mytable
bear - bear - dog - dog
bear - bear - dog - catsnake - snake - snake - snake
bear - bear - dog - snake
bear - bear - cat - bear
bear - bear - cat - dog
bear - bear - cat - catthis macro does it
bear - bear - cat - snake
bear - bear - snake - bearSub 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 - snakeEnd Sub
bear - cat - bear - bear
bear - cat - bear - dog
bear - cat - bear - catthe macro puts every permutation of 1,2,3,4 in col F (F1:F4)
bear - cat - bear - snake
bear - cat - dog - bearin G1:G4 there is a vlookup formula
bear - cat - dog - dog
bear - cat - dog - categ in G1
bear - cat - dog - snake.=VLOOKUP(F1,mytable,2)
bear - cat - cat - bear
bear - cat - cat - dog
bear - cat - cat - catcell G7 concatenates G1,G2,G3,G4
bear - cat - cat - snake
bear - cat - snake - bear
bear - cat - snake - dog

<colgroup><col span="14"></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top