Duplicates with Count

Help Activities
, ,
1

Hello All,
I have two excel files sheet 1 has unique values of policy numbers and sheet 2 has duplicates policy numbers. Here i need like if sheet1 policy numbers matches sheet2 policy numbers then in status column in sheet2 has value “Settled”. for unique policy number I need the count of value settled for that policy numbers and amount sum of that count. Please help me with this issue.

2

@ISBS_ROBOT

can you share any sample input and expected output

so that we can help

4

New Microsoft Excel Worksheet.xlsx (10.7 KB)
Please check the attached file. and revert back

5

Hi @ISBS_ROBOT ,

Could you maybe also provide us with the Expected Output representation for the Input provided ? There are some confusions around the data and columns to be cleared.

6

Can you please share the excel file ?

7

New Microsoft Excel Worksheet.xlsx (10.7 KB)
Please check the attached file

8

Hi ISBS_ROBOT,
Can you please repharse your query one more time. and try to explain the problem statement differently.
Also the excel you shared, in sheet1 Policy No are not unique, rather repeated.

9

Hello,
Sheet2 has unique policy numbers and sheet 1 has policy numbers with duplicates. So if Sheet2 policy num contains Sheet1 policy num then for that policy num, in status column contains “settled” then I need the count of status and sum of amount. Like for one policy num there are 3 value of settled in Status column then I need the count and also sum of column “TOTAL_AMOUNT_CLAIMED”.

10

Hi @ISBS_ROBOT ISBS_ROBOT,

what i understand is that based on data in Sheet1 we need to populate the data in sheet2.
So if in sheet1 there are 8 records of Policy No- 31200000000000000000 with status = Settled
then we want the count and the Sum of total amount of 8 rows to be populated in Sheet2.

We can do this by by following LINQ queries (groupby)

foreach currentGroup in mydt.Asenumerable.GroupBy(Function(x) x.item(1).toString)

"Status Count "+currentGroup.Where(fUNCTION(X)x.Item(5).Equals(“Settled”)).Count.ToString
"Sum Total Amount of all settled rows "+ currentGroup.Where(fUNCTION(X) x.Item(5).Equals(“Settled”)).Sum(Function(y) Convert.ToDouble(y.Item(6))).ToString

DupRow
DupRow892×896 20.3 KB

12

Yes,
count of settled for policy number and also sum from column “TOTAL_AMOUNT_CLAIMED”

13

upload xaml file here bro

14

AAAhhh…sorry I am unable to upload the xaml.
image

could you please try using the queries the i gave is working for you.
and then may be use a ADD datarow activity to create a new data and write in sheet2.

if not may be we look for alternative how to share the xaml.

Thank you

15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.