Hi everyone. I have some questions about a problem I’m trying to solve.
-We start from the data in 3 data tables. The maximum number of cells (the sum of the cells of such 3 data tables) is 9. (In the example shown below there are 4 oranges, 2 apples and 3 bananas = 9 in total). It’s just an example, because it could have been 3 oranges, 1 apple and 5 bananas (the sum is 9 again). The type of fruits is always the same (ORANGES, APPLES and BANANAS). It’s also possible that some of the fruits is 0 (for example, 9 orange and 0 apples and bananas).
-We have 3 data tables on the right. Each of them has 3 cells (so that the sum is 3+3+3=9).
What I want to achieve is that each of the tables on the right has only the same fruit. For instance, the best solution could be 3 oranges in Final Data Table 1, 3 apples in Final Data Table 2, and 3 bananas in Final Data Table 3.
As mentioned in this example, there are 4 oranges, 2 apples and 3 bananas, so it’s not possible to achieve that each of the Final Data Table has only one type of fruit. In this case, a good solution is shown below (3 oranges in Final DT1, 1 orange and 2 apples in Final DT2, and 3 bananas in DT3).
So the goal is that EACH of the Final Data Table has as many fruits of the same kind as possible.
I really need your help and I would really appreciate so so so so much if you could help me.
If you have any questions please do not heasitate to ask me.
Thank you very very very very very much!!!
Regards.
P.S.: Please, find attached the Excel file. File.xlsx (9.1 KB)
I was thinking that you needed to perform 3 steps:
join the tables to a new table
sort new table
split table into equally sized tables or array of tables.
Does that sound right?
I can’t get you a solution off the top of my head and short of time, but there is some useful info on manipulation datatables in vb.net or C# though online searches.
Hello again!
I understand exactly what you mean.
So let’s assume we start from the following situation (I mean we start from the tables joint):
I got to sort the table (please find the files attached): Main.xaml (9.6 KB) File.xlsx (9.0 KB)
After sorting the table, the new joint table is the next one (you can see it by playing the Xaml attached):
Apple
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Orange
After doing this, I have two questions:
How can I write such a data table in the Excel file so that It can be seen as the following picture? (I mean I do not know how to write in some ranges. I mean how to write in F5:F7, F10:F12 and F15:F17))
By doing it this way (sorting the table and then writing the table as it is), we can see in the previous picture that it’s not the optimal solution. The optimal solution would be as follows:
(I mean on the picture uploaded in 1) we can see that “Banana” is found in two different Final Data Table(Table1 and 2), and the optimal solution would be that “Banana” is in the same group(Table2)
I’m looking forward to hearing from you.
Thank you very very much, I really appreciate your help.
After sorting the table you can use a for each row activity with write cell activity in it and use a counter row … after every 3rd row, counter will reset and excel cell will be updated by 2.
Example:
Counter = 0;
Cell = 5;
For each row in Finalist
{
Write cell : input row value and cell F +Cell.tostring;
Counter = Counter + 1;
Cell = Cell + 1
If( Counter == 3)
{
Counter = 0;
Cell = Cell + 2;
}
}
I don’t know if it’s the best approach or if it’s everything you were looking for as a solution, but hope this helps.
PS: I didn’t annotate the variables, but they are straightforward. tablesize is the number of rows per table you want. Also, much of my conditions used are inside the value fields of the activities.
@ClaytonM
Thanks a bunch!
I really appreciate your help. The file and the instructions you sent me work very very well as I need.
I have one more question. I modified the “xaml” file so that instead of building a data table, we use read range ( C5: C13):
Sorted initial table in descending so I can output it backwards in original sorting order
Changed everything where needed to use the 3rd column for the fruit
Corrected a coding mistake where if count = 0 already on second loop, it was messing up
Changed “Add Data Row” activities so it can output 3 columns, and used the count as the index to match up column 1 and column 2 to the Fruit, along with table .Where (since it decreases by 1 each time)
