Having a doubt to rectify

Hello Everyone,

Let me explain my scenario.,

I have two different excel files Excel-A, Excel-B with different column headers.

What I have to do simply is compare between two datatable and remove the rows in excel B based on 4 column condition

Eg :
1st → I need to filter the excel -A and get only the rows that have “No” in the “Status” column
and this is done by filter datatable.
2nd → Now From Excel A (Get all the row values from 4 columns) and compare them with the Excel B rows and delete those rows if matched with those 4 columns in the Excel A

Note: Excel will have a dynamic number of rows may be little or many, So I have tried with For each row and it’s taking more time in my project and returning wrong output, Kindly give any suggestions in any method.

@Palaniyappan @lakshman @Srini84 @ppr @Yoichi @kirankumar.mahanthi1 @Nithinkrishna @codemonkee @supermanPunch

Thanks in Advance !!


And Here is the sample xlsx file
Excel A.xlsx (7.9 KB)
Excel B.xlsx (8.6 KB)


Give a try on following sequenceEqual approach:

Assign Activity:
LHS: Result | List(of DataTable)

(From d In dt1.AsEnumerable
Let cASet = {"dtACol1Name","dtACol2Name","dtACol3Name","dtACol4Name" } 
Let cBSet = {"dtBCol1Name","dtBCol2Name","dtBCol3Name","dtBCol4Name" } 
Let chs = cASet.Select(Function (x1) d(x1)).toArray
Where Not dt2.AsEnumerable.Any(Function (d2) cBSet.Select(Function (e) d2(e)).toArray.SequenceEqual(chs))
Select r = d).toList

then based on the filter result we can copy it to a datatable or clone the origin one
check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dt1.Clone

It should filterout dt1 rows where checked on the 4 cols no corresponding dt2 row is present

1 Like

we assume that

sample data has a copy paste issue and 754-Soap is not a concatenated value. Otherwise we do have another requirement

Actually, it will be exactly like this so I need to concatenate both strings of Excel A and compare them with Excel B’s value



In the query you given
Dt1 → Excel A
Dt2 → Excel B right?

I have modified your query like this

(From d In BuildDataMatchingDT.AsEnumerable
Let cASet = {“furnizar”,“Segment”,“Piata”}
Let chs = cASet.Select(Function (x1) d(x1)).toArray
Where Not Input_DTFromSource.AsEnumerable.Any(Function (d2) cBSet.Select(Function (e) d2(e)).toArray.SequenceEqual(chs))
Select r = d).CopyToDataTable

And it is returning the value of cases only but I want the output of cBSet and with all the columns (It will have up to 10-13 Columns)

If I interchange the dt in the query it throws an error like the column is not in datatable



It is not filtering the rows instead it takes all the rows from the datatable


Thank you so much @ppr for sharing the query

I had got the answer by following these steps

  • After filtering the excel -A and got only the rows that have “No” in the “Status” column.
  • Build Datatable and created a new table with three columns
  • Loop through the filtered rows and add data row to add the rows as per requirements like concatenating two-row values into one and adding that value to the new column
  • With the following query I got the required datatable

Input_DTFromSource.AsEnumerable().Where(Function(row) Not BuildDataMatchingDT.AsEnumerable().Where(function(r) r("FURNIZOR").ToString.Equals(row("FURNIZOR").ToString) and r("SEGMENT").ToString.Equals(row("SEGMENT").ToString) and r("PIATA").ToString.Equals(row("PIATA").ToString)).Any).ToArray


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