Thanks for response what i need is I have to remove duplicate rows and also the rows which i remove I need sum of particular column, for example if I have removed 3 rows and those 3 has a column P and P has value (10,20,20) so I need 50 at remaining row i have attached the input and output example
Hi @Chirag1991 , you were getting an empty result, since the data on my xaml was a test excel that I created.
I’ve loaded yours and with this 3 activities, I’m extracting your sums:
The workflow now uses your input. Be aware that the results are being stored in a dictionary for demo purpouses (var_dict_UniqueDescriptions), if you want to do anything else with that Sums, please use the following variables, inside the For Each Loop:
Hi, that is not what accepting output for your refernce I have attached the excel, in excel you will see one sheet as “Input”, so that what will what excel it look like and “output” sheet is what desired output
so if you will look you will see that sum of the respective column has to write on the distinct rows of duplicate, not below.
if you will look into an excel that "output"sheet has what it should be look like
so could you please create in same way, and i would request if you can create in data table because I would need to create excel
Hi @Chirag1991 , please note that this screenshots were just to demonstrate that the bot is doing the sum correctly and therefore, I did the sum manually in excel as can be seen in this image below.
- Values in Yellow and green on the image below, were added manually to prove the correct Sum being done by the bot.
The workflow I sent is NOT modifying (Not adding data neither removing data) your excel at any point.
If you want to modify the remaining rows, with the result of the sum of the duplicated rows, you can do it with the values of the following variables:
Again, this values will keep changing for each different set of rows, therefore please use them inside the For Each, and save them somewhere else, like I did with the dictionary.
Notice that the bot is typing the result on the “OutputDemo” tab. Update that part according to your requirements. Also I’m getting each combination of BIC + Currency to build the last Datatable. Make sure to check that the result is the expected.
the only difference between your output and mine, are the dates and the number of columns. My workflow considered the same date, and you only need to add the filter to each unique date, inside the for each.
→ This can be done with the Filter DataTable activity, or with LINQ, as you prefer.
You only need to add this extra filter, and for each date, get your new row based on the filtered result. And remove the unwanted rows before typing it back to the output sheet.
Please remember that the forum is used to provide support, and we will not normally develop full functional workflows as per request.
→ Feel free to reuse any components on the actual workflow, to make that extra filter, and you’ll have your desired output.
On this example you can see that the array of objects accepts Strings, Int32, DateTime, and so on. therefore you can use it also on the “Add Data Row” activity, under “ArrayRow” Property to get custom formats.