How to extract only the duplicate rows based on a certain value

I have a excel sheet , where I am searching a name(Eg: AAA). The name might appear in multiple rows. I need to extract only those rows where AAA is present and copy to another datatable

Book1.xlsx (8.6 KB)

Image shows how the output should look like

Kindly help
TIA

Hi,

Can you try FilterDataTable activity as the following?

Regards,

Thanks.
But my input file is on similar basis , where i have more than 900 rows.
and my search item is not hardcoded. Like i will be looping a column from a datable and searching each value if it is found in another datatable.
So how do i do that using filter datatable wizard using column number rather than column name

Hi @shilpa_p

you can achieve this using linq

(From d In dtData
Group d By k=d(0).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

image

Hope this helps

1 Like

Thank you :slight_smile: . I will try

1 Like

Hi,

FilterDataTable activity is not very suitable for complex/dynamic filtering.

Can you share what you want to achieve as result? We might be able to suggest LINQ expression for 2 datatables.

Regards,


In the above image, I will be looping through each value in the column and search if it matches in the excel file(book1) attached above. Then i have to extract the rows containing that value and copy to another datatable
Out put should be as shown below

Hi @shilpa_p

here is a workflow of your sample excel
Sequence.xaml (5.6 KB)

Thanks

Hi,

Thank you for sharing. We can achieve the requirement using FilterDataTable, I think.
Can you try the following sample?

Sample20220407-2.zip (15.1 KB)

Regards,

Thanks.
Can i give column number instead of column name in column field

Hi,

Can i give column number instead of column name in column field

Yes, as the following.

image

Regards,

1 Like

Thanks it works. I just need one modification.
Suppose the first value that i am searching is AAA, then it should keep only AAA related rows. next if search value is CCC, it must keep CCC related rows
Can this be done in that

Thanks

1 Like

I will try. Thank you :slight_smile:

Hi,

FYI, If you need only duplicated rows, please add If activity as the following.

Sample20220407-2v2.zip (15.3 KB)

Regards,

1 Like

Sure I will try

i dint understand your context @shilpa_p can you brief me more

so basically u need only 1 st duplicate result

??

No .
I have two excel files
from 1st excel file(data) , i will be looping each value in the column and checking that exists in excel file-2(book1)
if a match is found, say for 1st value AAA, then all related columns should be extracted.
the same applies for all values in excel file(data.xlsx).
So when i search and a match is found, in the output i must get only that value related rows. not others

These above images are reference of how output should look like

Yes i Got it @shilpa_p

Please find the attached code

Sequence.xaml (9.0 KB)

Thanks

@shilpa_p Try with this query

Assign Result_DT = DT1.AsEnumerable().Where(Function(r1) DT2.AsEnumerable().Select(Function(r2) (r2(0).ToString) ) .Any(Function(x) x=(r1(0).ToString) )).CopyToDataTable

Sure. Thanks I will try