Remove unmatched rows

[jntrk]

Hey everyone,

I have 2 excel documents. One of them is “main” excel, the other is lets say “remove” excel.

These 2 excels have 2 columns which have the same type of values. “Doc. Number” & “Item”.

I want to remove the lines from main excel where these “doc number” & “item” matches.

example:
Doc.Num/Item (main excel)
1/1
2/1
2/2
3/1

Doc.Num/Item (remove excel)
10/1
2/1
2/3
3/2

I want to remove in the main excel the row where doc.num=2 and item=1 (for this example)

I would appreciate every bit of help.

[ppr (Peter Preuss)]

@jntrk
welcome to the forum

the case looks like it could be solved with the help of set operations

find some demo here:
DataTable_SetOperations.xaml (8.9 KB)

So we could do it with an except:
dtMain.AsEnumerable.Except(dtRemove.AsEnumerable, DataRowComparer.Default).CopyToDataTable

ensure following:

AsEnumerable is not a member of 'System.Data.Datatable'

Hi @lucas.mcgonagle EDIT 20.10 fixed this issue for newly created projects. You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup.

in case of empty results are to expect apply following pattern:

[jntrk]

Hi @ppr

Thank you ,

The thing is I have more columns on data table but I just want to compare 2 like you did. So I believe the solution you gave will compara all columns. How can I compare only the doc.num and item column? Or can I?

[jntrk]

To give you a better understanding remove excel only has doc.num. and item column
main excel has doc. num, item and 8 more columns. I appreciate your help very much.

[ppr (Peter Preuss)]

@jntrk
ok I will have a look on it

[ppr (Peter Preuss)]

@jntrk
Find starter help here:
RemoveMatches_2Col.xaml (10.3 KB)

Kindly note: the demo set is working with a reduced sample dataset (3 cols instead of 8). But the main thing is the check on the 2 key columns and this is implemented. Just adopt it to your needs and Columnnames/index.

Let us know your feedback

[jntrk]

You are a god. Thank you very much, I implemented it to my process and its working.

to clear everything:

IF drResult.Count > 0 (means there matching rows)
Then: result= matching rows removed.
Else: result = main excel meaning nothing is changed?

Did I get it correctly?

[ppr (Peter Preuss)]

@jntrk
in the case we want to get back a datatable (while filtering with DatatableVar.Select(…, or a LINQ) we can use CopyToDataTable. But in case of empty results the CopyToDataTable throws an exception,

The Pattern above is denfensive as it returns a list of DataRows (from LINQ result) and if there are Datatrows returned then it executes the CopyToDataTable. Otherwise it clones the origin datatable and creates with this an empty datatable with the same structure.

Fell free to do your further testing. Once it hass passed the test just close the topic with marking the solving paost as solution