How to extract duplicate data from a same excel?

Help Activities
, ,
#1

the feature built in UIpath only remove the duplicates in the excel
but I wanted to extract the similar data “rcp_no” instead of removing it using ‘remove duplicate row’
how to get the duplicate row and write it into new excel?

from this excel
main

into these 2 result
result

duplicate

testing.xlsx (13.0 KB)

thanks in advance!

#2

i tried join table and filter table
couldn’t get the result I wanted

#3

Check this below workflow, @terrylim11
Uipath_GetDuplicateRows.xaml (6.4 KB)
Hope this may help you :slight_smile:

#4

it works! thanks!
but do you mind explain this?

DuplicateRowsDt = (From d In TestDt Group d By k=d(“rcp_no”).toString.Trim Into grp=Group Where grp.Count >1 Select grp.toList).SelectMany(Function (x) x).CopyToDatatable.AsEnumerable.GroupBy(Function(x) x(“rcp_no”).ToString.Trim).Select(Function(y) y.First).CopyToDataTable()

it is slightly complicated. hahahahha

#5

Ya sure… @terrylim11
From the above linq query, first you will get all the duplicate rows,
i,e. - (From d In TestDt Group d By k=d(“rcp_no”).toString.Trim Into grp=Group Where grp.Count >1 Select grp.toList).SelectMany(Function (x) x).CopyToDatatable.

Then I used this code to get unique among dupliacte rows :- .AsEnumerable.GroupBy(Function(x) x(“rcp_no”).ToString.Trim).Select(Function(y) y.First).CopyToDataTable().

Hope this may help you :slight_smile:

1 Like
#6

i changed the content inside the excel
it prompt error
image

image
how to fix?
tks in adv

#7

Can you send me what you have changed? @terrylim11

#9

is the file too many rows?

#10

Since their is no duplicate rows in “rcp_no” column because of that you where getting this error, check this updated workflow @terrylim11
Uipath_GetDuplicateRows.xaml (7.3 KB)

:+1:

#11

same error
hahahaha

image
image790×365 11.2 KB

image

#12

Can you send me the condition in the if activity @terrylim11 ??..
Whether the condition is like below code,
(From d In TestDt Group d By k=d(“rcp_no”).toString.Trim Into grp=Group Where grp.Count >1 Select grp.toList).SelectMany(Function (x) x).Count > 0

#13

Sorry, i didn’t get the notification via email.

Yes exactly the same. I didn’t change any.

#14

Ya change it to this below code, @terrylim11
(From d In TestDt Group d By k=d(“rcp_no”).toString.Trim Into grp=Group Where grp.Count >1 Select grp.toList).SelectMany(Function (x) x).ToArray().Count > 0

Hope this may help you :slight_smile:

#15

good job really appreciate
(From d In TestDt Group d By k=d(“rcp_no”).toString.Trim Into grp=Group Where grp.Count >1 Select grp.toList).SelectMany(Function (x) x).CopyToDatatable.AsEnumerable.GroupBy(Function(x) x(“rcp_no”).ToString.Trim).Select(Function(y) y.First).CopyToDataTable()

2-TestDt.AsEnumerable.GroupBy(Function(x) x(“rcp_no”).ToString.Trim).Select(Function(s) s(0)).CopyToDataTable()

if you could give little bit explanation of both queries really appreciate

#16

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