Compare 2 datatables, write match to next column where they match

[jntrk]

Hey everyone,

I have 2 excels (1 is main excel, second is check excel) , what I need is compare these 2 datatables’ “Material” column. On the matching rows I want to write match/no match to “State” column on the main excel.

I want to do it without using loops. I know there is a way to do it but I dont know how

I appreciate every bit of help

[prasath17 (Guru)]

@jntrk - please search in forum …I have seen exactly similar post(s) before …

[jntrk]

Actually I’ve searched but couldnt find the a solution for this.

[supermanPunch (Arpan)]

@jntrk Check if the below posts can help you. If you still need more help, can you address your problem in a bit more detail, maybe with Sample Inputs and the expected output that you need.

Insert a value in a column checking the other two column values

@Akhila_Ashok have a look on the demo doing it with LINQ, Select-Add.Row Approach Demo_WhenPresent_ThenUpdate_OtherCol.xaml (7.9 KB) LINQ is about retrieval (getting something) and updating a column is not returning something. With invoke code and the setField Method an Update can be done in case of it is absolutely needed Update_InvokeCode_Demo_SetField.xaml (6.6 KB)

[Mulya_Habibi_Tullah]

Comapring multiple coulmns in Excel Files

Hi All, I have two Excel files Excel1: [image] Excel2: [image] I need to compare first two columns in both the sheets and if matched, update the third column in first sheet taking value from second sheet 3rd column. Also I want to validate if a cell in 1st sheet is empty then write a exception. Please help me friends. I have gone through many topics but can’t find the exact solution.

Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

Hello Community Use Below Code to Compare 2 Datatable Column and Get Matched and Not Matched Records Note- Based on your datatable column datatype update datatype here with column field. To Get Matched Records Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable() To Get Not…

[jntrk]

Thank you for your links but the problem I face is not on matching the excel I can get them compared and output the matched/non-matched tables seperately.

To example what I need:

MainExcel (dont mind blah blah columns the excel has 9 columns, thats What I meant)

.
Check Excel

I want to make the matching row as matched on the main excel (without corrupting the excels format, its xlsm-macro enabled doc. so I dont want to break it )

[jntrk]

@supermanPunch any ideas?

[supermanPunch (Arpan)]

@jntrk

I want to make the matching row as matched on the main excel (without corrupting the excels format, its xlsm-macro enabled doc. so I dont want to break it )

@jntrk We can do it using datatable and Linq approach but if you need the format to be not modified we would have to think of an alternate, or can you tell me what kind of format is involved for the file ?

[jntrk]

Well there are some macros enabled, and the headers have filter option enabled on them. Linq is the only approach in my mind as well, but I couldnt make it work, I am new to using queries. If we read the excel range with “preserve format” use linq then write with preserved format, thats the only solution I have

Could u help me wtih the linq?

[supermanPunch (Arpan)]

@jntrk If you provide the sample data with the same format (i.e having macros enabled and other formats), we can try using Linq and give you the result, so that you can check if it is the expected output that you need.

[supermanPunch (Arpan)]

@jntrk Can you check this workflow :
MatchedValuesUpdated.zip (87.8 KB)

I was not able to implement it completely in Linq and Update the excel, as it would lead to disrupting the formats present. But I have used the Material Code from Other Sheet to fetch the Indices of the matching Code from Raw Materials Sheet and then Update the Required Column Value using Write Cell Activity.

However the format of the Number turns to String.

Check if it satisfies your requirement.

[jntrk]

It satisfies Thank you very much.