Write Data Tables in different Ranges (Excel)


#1

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)


#2

@Nithin_P @ddrdushy1 @Florent_Salendres @vvaidya @jibanjyoti @Dominic @nikitha.hanumanthara @arivu96 @sarthakj @andrzej.kniola

Thanks to everyone!!


#3

@aksh1yadav @palindrome @ClaytonM


#4

Hey.

I was thinking that you needed to perform 3 steps:

  1. join the tables to a new table
  2. sort new table
  3. 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.

Regards.


#5

Hello.

Thank you so much for your answer. I understand what you mean but if the number of fruits is different, I can not equally the tables…

Apart from that, I have no clue about how to do it…

Thanks a lot again.


#6

Sorry, I meant, equally by the number of rows.
For example,
4 Oranges, 2 Apples, and 3 Bananas would be put into a table like

Orange
Orange
Orange

Orange
Apple
Apple

Banana
Banana
Banana

equally as in number of rows per table.

Is that right or am I still wrong?

Regards.


#7

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):
possible1

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:

  1. 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))

possible2

  1. 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:
    possible3
    (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.

Best regards!!!


#8

Answer for point 1

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;
}
}


#9

For this you ll need to make an algorithm… I don’t think just by sorting you ll get an optimum solution.


#10

hey @pal1910,

I played around with some basic logic and here was my approach:

  1. Store all unique items into a table
  2. Store the count for each unique item in same table
  3. Loop through each unique item using a counter until all counts are less than the tablesize
  4. Add Data Row with current item
  5. Decrease the count by 1 for that item
  6. Increase to next item index only when its count is less than tablesize (using modular math)
  7. Also Add Table title and blank rows outside of the tablesize
  8. Loop through each unique item again to add remaining leftovers
  9. Write table to range and end

I hope that is clear.
Here is my working .xaml:
tablewriting-pal1910.xaml (33.2 KB)

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.

I wish you luck! Regards.

C


#11

@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):


What I want is just that apart from getting what I asked before (I mean:

I just need that the elements on the left of each fruit to be located also on the final table. I mean:


I think this question is much easier than the previous ones but I am stuck on it…

I attach the files here:
File.xlsx (9.3 KB)
tablewriting.xaml (32.1 KB)

Thank you very very much again.


#12

I gotcha.

  • I added 3 columns to the Final table
  • 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)

Here you go:
tablewriting-pal1910.xaml (39.9 KB)

Regards!


#13

Thanks a lot @ClaytonM
I completely thank you for your help.
Regards.