Get a single row of duplicate rows. Need urgent help

Help
, ,
1

Hi all,

I need urgent help, would really appreciate if anyone can. example.xlsx (10.7 KB)
I have uploaded this xlsx as an example . Please refer to it.
My problem is when “PC” and “Vendor” are same for line items then i have to add the amount total and print that single row of the rows which are duplicate, where age is greatest.
Hope the excel file would help in uderstanding.

Thanks a lot !! Anyone Please help

@Palaniyappan
@aksh1yadav
@arivu96

1 Like
2

@Shikhar_Tandon

Can be done with the help of a GroupBy Statement

(From r In dtData.AsEnumerable
Group r By PC=r(“PC”), Vendor=r(“Vendor”) Into grp=Group
Select grp.OrderBy(Function ( r ) CInt(r(“Age”).toString.Trim)).Last()).CopyToDataTable

PFA demo XAML here: Shikhar_Tandon.xaml (6.1 KB)

1 Like
4

remove the

image

all rows and try it will works

Thanks

5

Hi @ppr,
Thanks for the reply but,
when duplicate rows are combined to one i also want to add the amount that is for matching rows amount total should be displayed in the final row. Please suggest what to do .
Also when running this workflow extra columns are adding to the excel sheet automatically.

Please help !!

Thanks !

6

Hi @ppr,

Tested with multiple rows. This is not working exactly proper if PC number matches and Vendor number does not match then also the query is returning that row as PC number matched. Please Help.

7

@Shikhar_Tandon
the fastest was to come Up to the solution Could be that along the Input example you are adding the expected Output as an Excel or description. This helps for solution development

8

Hi @ppr,

example.xlsx (10.7 KB)
result.xlsx (8.2 KB)

i have attached the example again and the result also in the result only one row is returned
with data 21 6 787 200 121 , 200 being the total of amounts in the duplicate rows referring PC and Vendor. Please help.

Thanks !!

9

@Shikhar_Tandon

Let me summarize my understanding of your requirements:

  • groups / duplicates are recognized by same PC, Vendor Info
  • If we have a group, having a count more then 1 Rows, then return:
    • values from the row with the greatest age, Amount is to sum up from all group amounts

PFA demo xaml here:
Shikhar_Tandon2.xaml (9.4 KB)

Kindly note:

  • I cleaned up unnecessary columns
  • for protoyping i partly used column index - feel free to shift to column names

Let us know your feedback

1 Like
10

Hi @ppr,

Thanks a lot it is working. Can you please tell the meaning of Enumerable.Range(0,dtData.Columns.Count - 5) this code and also please specify where i have to use column names because mostly i see you have used column names, so where is indexing. ?
Please mention column names in the code so that i can get reference, rather than indexing
thankss a lot . !! please reply

11

@Shikhar_Tandon

Enumerable.Range(StartIndex, Length) is creating a series e.g. 0,1,2,3,4 …
This allowed to loop over the no of unnedeed columns (count - 5, as we want to keep the 5 columns)

In this line, Columnindex are in use. I suggest to change it accordingly to your final columnnames
Let nr = New Object(){lr(0), lr(1), lr(2), grp.Sum(Function (x) CDbl(x(“Amount”).ToString.Trim)),lr(4) }

Great, that it is working. So lets close the topic with flagging the solving post as solution. Others can benefit from it.

1 Like
12

Hi @ppr,
Thanks a lot for the answer.

just needed one more help, I have one main dt from where i would perform this opertion, after performing this when i will write again to that dt an extra line item would be added in the main dt so if i want to delete the rows which i have used here from the main dt what to do please help…
thanks a lott !!

13

@Shikhar_Tandon
I am not sure if got you in total

maindt is holding the data and the result of LINQ statement is written back to maindt?
the part with the extra line and deletion i didn’t understand.

Can you please help us and show us sample rows and/or giving some more details? Thanks

1 Like
14

Hi @ppr,

thanks for the reply,
Basically what i am saying is say their one datatable named DT1 which has duplicate PC and Vendor when i applied the linq query i got the resultant line item, so what i want is that these duplicate rows be removed from the DT1 and the resultant line item to be placed in that DT1.
that is all the work happens in the same DT1, the duplicate rows with same PC and vendor to be removed and the final line item obtained when adding the amount of these rows to be placed in DT1.

thanks. Please feel free to contact if any queries.
thaks a lott!!

15

@Shikhar_Tandon
ok now its more clear:

  • remove the duplicates
  • insert the computed row (oldest, summed up amount)

I will have a look on this later after my Job work

16

@ppr

yes. remove the duplicates row and place the computed row on the same dt you got that right.

Thanks a lot. Please have a look and provide a solution

Thanks !!

17

@Shikhar_Tandon

PFA updated XAML:
Shikhar_Tandon3.xaml (12.3 KB)

Kindly note: there were some variable renamings necessairy. Take notice of this when e.g. merging to your code.

18

Hi @ppr,

Really appreciate your effort thankyou, But Capture

here you see, i am not getting the desired result, result should be

as PC and Vendor of row 3 and 4 and the age is common or greater whatever.
the final result should include the other rows which have no connection with this and row 3 and 4 should be deleted and in their place only one row should be inserted which is the result of added amount of that tow duplicate rows,
let me show you the result should be,
PC , Column1 , Vendor , Amount , Age
23 , 4 , 787 , 101 , 88
11 , 4 , 76 , (231 +12) ,777
---- ---------243----------
11 , 5 , 76 , 65 , 77

this should be the result(only these rows) rows 3 and 4 vanished and is replaced by one single row with amount as total and one with greater age.

Thanks!! looking forward for your reply.
Thanks a lot !!!

19

Send me the Excel with this data. But with your specification from above groups are to build for same PC and vendor. All three rows are having the Same PC and vendor Info.

An additional reaction on age were Not mentioned by you or specified. Maybe you reformulate your requirements in total and complete, so solution will Cover all points