How to use permuation and combination in excel uipath

Hi Suppose there is an collection or datatable in which there is column

Team 1 Team2 Team 3
RIP kevin raven
Sam nick john
ben sarah peter
watson arthur Ned

using these team (1,2,3) I want to make one team with 8 members and I want to try all permutation and combination is possible?

Need your Help!!!

1 Like

Hi @Addy_619,

I guess, I took this question quite seriously. After 3 hours of trying to solve it. Python object conversion error has made me stop for today.

Challenge:
I cant seem to get the correct conversion scheme from python object, which is a List of List ([[str],[str]] to an equivalent in .Net argument. If you can figure that out, then you have a solution here.

Approach:

  1. To start of you will want to make the multi column data into 1 single array of strings with player names. That is your sample space “n”. Now in that you are interested in making a team of 8 = “r”(combinations)

  2. Then you can calculate the permutations and combinations given that you want 8 players per team without Repetition (this is important as formulas will be different for with repetition) you have
    Permutations: 19958400
    Combinations : 495

  3. Now that we know the permutations and combinations. We can apply them to the test data.

  4. To get the these results on array format, I had to go back to Python and use the code example from from GeeksForGeeks. I tested it on your data and it works as expected, I did not dare run it on the permutations as it would be just too long a wait for it to execute.

  5. In the zip file you will find a log.txt, which shows the python output when UiPath runs the method. That are the combinations possible on your data. The Workflows are generic and you can reuse them to change “n” or “r”.

I have not made the GetPermutationArrays.xaml, but it will be similar as GetCombinationsArrays.xaml, and I think you can figure that out yourself. Also remember to install python before you run this - I used 64 bit - Python 3.6.8.

Resources:

  1. Permutations and combination calculator and formulas : https://www.mathsisfun.com/combinatorics/combinations-permutations-calculator.html
  2. My suggested approach: GetCombinations.zip (12.2 KB)

Hope this helps!

4 Likes

Thanks alot I will try it out just want to know more about challenge because in log.txt I can see output which means you have executed successfully

@Addy_619

have a look here:
NTeamCombinations_StackApproach.xaml (12.2 KB)

May I ask you to check the result in the generated Excel (rewire the path according to your environment)

Once it has passed the tests I can elaborate more on the implementation

Hi again @Addy_619,

I do have some inputs to the question. This question is related to Combinations and not Permutations.

You will not need to use permutations for this problem if you do not care about the order of the 8 players from the pool of 12. If you do care about the order of the players and possible permutations, then you have to first get the 19958400 permutation arrays and filter the result according to the required order to see how many permutations of the required order can be found.
Example: Imagine you have a team of 8 and you only want teams where RIP,kevin,Sam are the starting players. Then you would want to check for possible permutations satisfying this order.

But for the question asked, you only need a team of 8 from a pool of 12 players and therefore Combinations = 12P8

Regarding the challenge I faced, that is purely due to my lack of know-how when it comes to casting objects from a Python-Object to a .Net object in UiPath. Someone who is better at this can may be help @bobpeers. When you run Main.xaml, you see that you will get an error mentioning “Get Python Object: Error converting Python object”. Python did its job, but the .Net argument I used is wrong (List[List[Str]]) see yellow marks.


Combinations found from Python output List[List[str]]: image

@ppr / @bobpeers, do you know how to cast python-object List[List[Str]] to a .Net object. What is the correct TypeArgument to be used here? Should it not be List[List[str]] as well?

@Addy_619
I agree to @jeevith that is related to combinations and that is recommendable to focus on team member combinations without taking the team positions into account.

For this my demo uses a stack approach

Havin a set of members:
List(12) { “A1”, “A2”, “A3”, “A4”, “B1”, “B2”, “B3”, “B4”, “C1”, “C2”, “C3”, “C4” }

Starting with 12 teams (Team stack with level 1):
List(12) { “A1”, “A2”, “A3”, “A4”, “B1”, “B2”, “B3”, “B4”, “C1”, “C2”, “C3”, “C4” }

now in the first iteration:

  • look for each team member in members for all members not present in the particular team

  • create new combinations (team stack size: 2) of team member and 1 free member:
    Result for A1:
    List(66) { “A1,A2”, “A1,A3”, “A1,A4”, “A1,B1”, “A1,B2”, “A1,B3”, “A1,B4”, “A1,C1”, “A1,C2”, “A1,C3”, “A1,C4”

  • order all teams by its members and remove duplicates: So A1,B1 and B1,A1 are the same and the duplicates are removed (As we said, member positions in the team is to ignore)

do the same for the next level (3):
List(220) { “A1,A2,A3”, “A1,A2,A4”, “A1,A2,B1”, “A1,A2,B2”, “A1,A2,B3”, “A1,A2,B4”, “A1,A2,C1”, “A1,A2,C2”, “A1,A2,C3”, “A1,A2,C4”, “A1,A3,A4”, “A1,A3,B1”, “A1,A3,B2”, “A1,A3,B3”, “A1,A3,B4”, “A1,A3,C1”, “A1,A3,C2”, “A1,A3,C3”, “A1,A3,C4”, “A1,A4,B1”, “A1,A4,B2”, “A1,A4,B3”, “A1,A4,B4”, “A1,A4,C1”, “A1,A4,C2”, “A1,A4,C3”, “A1,A4,C4”, “A1,B1,B2”, “A1,B1,B3”, “A1,B1,B4”, “A1,B1,C1”, “A1,B1,C2”, “A1,B1,C3”, “A1,B1,C4”, “A1,B2,B3”, “A1,B2,B4”, “A1,B2,C1”, “A1,B2,C2”, “A1,B2,C3”, “A1,B2,C4”, “A1,B3,B4”, “A1,B3,C1”, “A1,B3,C2”, “A1,B3,C3”, “A1,B3,C4”, “A1,B4,C1”, “A1,B4,C2”, “A1,B4,C3”, “A1,B4,C4”, “A1,C1,C2”, “A1,C1,C3”, “A1,C1,C4”, “A1,C2,C3”, “A1,C2,C4”, “A1,C3,C4”

and so on.

the main building part looks like this:

the implementation was based on strings (thats why it is using split and join), due the duplicates identification need and there was no List to List is equal content comparer in place.

find relinked demo XAMl here:
NTeamCombinations_StackApproach.xaml (12.2 KB)

2 Likes

give a try on:

  • get python object and assign it to .Net type: List(Of List(Of String))
  • try directcast
  • if all fails try to exchange json representations and deserialize it later
1 Like

Thank you @ppr, the third option worked.
i.e., Tell python to return combination values as json string and not List[List[Str]]. UiPath recieves this json string as normal string and then we use Deserialize Json activity (according to our required casting requirements i.e., List[List[Str]]. Each team is therefore a List[str] in the ForLoop see image.
The first option, I had tried before and the second one was new to me, but that did not work. Third was the lucky charm!

@Addy_619

To get the raw string output of all combinations, you can also use Log Message --> CombinationinUipath (which is already a string)

I am reuploading the files (only GetCombinationsArrays and the python script are modified).
GetCombinationsWorking.zip (12.3 KB)
Thanks for the weekend puzzle! :smiley:

1 Like

Instead of A1 B1 Can we get names of player in excel

got it can we get output of all comination in excel or in .txt file it would be really helpful

@Addy_619
for the initial development I would suggest to work with the A1,A2 … Codes, as it is helping for correctness validations.

Sure, later can it work with the player names e.g. directly or by a mapping.

Kindly note: demo xaml is writing the result to excel

That is quite straight forward. You already have arrays for each combination. Then all thats left to do is initialize a Datatable (Build Datatable activity) with 9 columns (1 Team name, 8 Players) and add rows to it in a for loop where each row contains 8 players and team name for example.

When the Datatable is ready, you either write it to a csv or a xlsx file by using Write CSV or Write Range activities respectively. Output can look like: TeamCombinations.xlsx (23.5 KB)

These changes are made in this workflow. Just replace the old file in the project folder with this file and when you run the Main.xaml output will be saved in both .csv and .xlsx: GetCombinationsArrays.xaml (17.7 KB)