5

I have this problem whereby I have multiple list of values and I need to get all possible permutations, so basically i have

List A = 1,2,3,4,5 List B = 10,20,30,40,50 List C = 5,4,5,2,1 List D = a,b,c,d,e

Each of them will be in a column adjacent to each other so the first row should have 1,10,5,1 followed by 2,10,5,1, and so on. Is there a way to get this list without using Macro?

Any help would be appreciated thanks!

Art Zahar
  • 155
  • 1
  • 3
  • 7

3 Answers3

5

You can create permutations by implementing a counter that cycles through your lists until it reaches the end.

For example let's say your lists are in columns A and B, and you want an array of possible permutations in column C.

Start with an if statement to bound your data. If the index of the current row minus the first row plus 1 is greater than the count of possible permutations, then we can leave that cell blank, otherwise, we want to return one of the permutations.

=IF(ROW()-ROW($C$1)+1>
COUNTA(A:A)*COUNTA(B:B),
"",

Now we need to create our counter. To do so, we will use the INDEX formula combined with the INT formula. Here we are essentially saying, look at list A, loop through it according to list B, and if we reach the end of list B, go on to the next value in list A, restart. This will give us an output of every value in list A multiplied by the number of values in list B.

INDEX(A:A,INT((ROW()-ROW($C$1))/COUNTA(B:B)+1))

If you need a text separator for the permutations between the lists, you can use the & symbol. 

&" | "

Now that we have the number of possibilities for list A, we can join list B. Using the INDEX function and MOD function, we can loop through list B and print its values until there are no permutations left to print.

&INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))+1))

Your function would look like this: 

 =IF(ROW()-ROW($C$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($C$1))/COUNTA(B:B)+1))&" | "&INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))+1))

The same concept can be applied to additional columns; simply modify the counter to account for additional permutations.

For more than two lists, you need to take a different approach with your counter. Try this formula out in Excel. With lists in columns A, B and C, place this formula in column D and drag down to the number of possible permutations:

 =IF(ROW()-ROW($D$1)+1>COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C),"",INDEX(A:A,MOD(INT(INT((ROWS($1:1)-1)/COUNTA(A:A))/COUNTA(B:B)),COUNTA(C:C))+1)&" | "&INDEX(B:B,MOD(INT((ROWS($1:1)-1)/COUNTA(B:B)),COUNTA(C:C))+1)&" | "&INDEX(C:C,MOD(ROWS($1:1)-1,COUNTA(C:C))+1))
Trevor McCormick
  • 366
  • 1
  • 3
  • 12
0

A macro based solution would be ideal. I am curious to know why you wouldn't want to use a macro.

If you change your mind, here is something that might interest you:

I developed some VBA code to do exactly that a couple of years ago. It involves a for loop that recursively goes through the sets. The code can handle an unlimited number of sets, but we are limited by the excel sheet of course. It's relatively easy to call. You can select lists through the interface, or call the macro yourself from your VBA code.

Give it a shot and let me know what you think.

Check out the post here

Ejaz Ahmed
  • 598
  • 7
  • 13
-1

For anyone who needs permutations, it is much easier using sql if you can. Just make one table per list wih respective values and then do a cross join between them:

ex using sql server (can be done in any DB):

SELECT        dbo.CloseAs.*, dbo.Connectivity.*, dbo.NumberOfPilots.*, dbo.PreviousSubmission.*, dbo.SubmittedData.*
FROM            dbo.CloseAs CROSS JOIN
                         dbo.Connectivity CROSS JOIN
                         dbo.NumberOfPilots CROSS JOIN
                         dbo.PreviousSubmission CROSS JOIN
                         dbo.SubmittedData
paketman
  • 169
  • 1
  • 3