Hi I do have an excel where I have duplicate rows also
I have to find duplicate rows in basis of multiple column and once it found I have to add " P column value at one row and then I have remove duplicate rows
So, basically first I have to add duplicate rows value and then I hace to remove duplicate rows
hi @Chirag1991 !
You can find the rows that are identical (all columns match) by using this LINQ below:
var_DT.AsEnumerable.GroupBy(Function(i) i.Field(Of String)("Col1")).Where(function(y) y.Count()>1).Select(function(z) z.Key)
Example of the input:
As seen in the image, rows “c/d and e/f” are duplicated. The result of this Linq will give you the following:
On a later stage, you can use the Lookup DataTable activity, to identify the indexes of that rows, and therefore add the required value that you need.
Hope it helps!
Ignasi
DuplicateRows.zip (43.7 KB)
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
Got it!
So from duplicate rows, you need to sum the column P right?
Hi @Chirag1991 , please find below the updated xaml.
I’ve used the following LINQ expression to sum the values of the Descriptions that are duplicated.
the output (just for demo) is a dictionary, of the description, and its own sum of amounts
var_InputFile.AsEnumerable.where(function(y) y("Description").equals(description)).Sum(Function (x) If(Double.TryParse(x.item("Amount Bought (MT300)").ToString, Nothing), Double.Parse(x.Item("Amount Bought (MT300)").ToString), 0)).ToString
DuplicateRowsCount.zip (45.1 KB)
hope it now suits your needs!
Ignasi
Thanks I will check and will update you what I need I hope you got it I will try and update
Hi I did run I get no output
And distinct filter will be combination of
“BIC”, message type , place of Custody, value date , currency bought, currency sold and
If you will see column Amount bought and Amount sold . For these two columns I need sum so if you will see excel you will find
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:
Best Regards,
Ignasi
DuplicateRowsCount.zip (96.4 KB)
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
P_ST64 - Forex provisioning report - AMAS.xlsx (58.5 KB)
this the excel which has “input” sheet and “output” sheet
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:
var_SumAmountBought
var_SumAmountSold
Sure,
I will try it .
Meanwhile if you can tell me how we can put in an excel will be helpful…
Hi @Chirag1991 I did add the part you need.
It is just a matter of getting the data that you obtained on the Dictionary, and wit that data, prepare a DT that you can write on the “Output” tab.
Find attached below the workflow.
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.
DuplicateRowsCount.zip (98.5 KB)
Update us here if the workflow fits your request!
Best Regards,
Ignasi
I have attached the screen shot, if you look into output sheet, that is expected output sheet,
so the sum as per above logic is not correct and line of items also not correct.
if you see output sheet 5 lines are coming that should be output. Even the rows who are already unique they should be there too
Also if you will see your output, Trade date and Value date is coming wrong
Good morning @Chirag1991,
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.
Best Regards,
Ignasi Peiris
Okay I did that and thanks one thing if my input header type is text and row type is custom, and in output sheet it is coming as text for rows, how can I make it custom?
On the example workflow, the datarow array, is introduced as “Array Of String”, but if you want to have custom data in it, you can change it to “Array Of Object”:
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.
thnks
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123