There are 3 excel files sheet1,2,3
If sheet1 column1=sheet2 column2 then the sheet1 column1 data should come in sheet3
how to put this condition
Could you share the input files . In th meanwhile you can try the below syntax:
dt3= (From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("column1") Equals row2.Field(Of String)("column2")
Select Sheet3.Rows.Add(row1("column1"))).CopyToDataTable()
dt3 is of DataType System.Data.Datatable.
Regards
(From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable() On row1(0) Equals row2(1)
Select dtResult.Rows.Add(row1(0))).CopyToDataTable()
I do this process for 1 Excel file.Please Take a for loop to iterate all files in the folder.Please Change the column numbers as per your Requirement in the Linq query.
Hope it helps You
there are 167 columns its very hard to declare every column is there any alternative?
Book 1.xlsx (371.3 KB)
Book2.xlsx (364.6 KB)
resultant.xlsx (10.7 KB)
there are three excel files book1 every column should compare with book2 columns
condition : if book1 column 1 = book2 column1 then the book2 column1 data should write in resultant sheet
here the column names should be matched
hi @anjani_priya
Book1.xlsx
Book2.xlsx
the column names values doesn’t match. Please check provide correct input files
Regards
column names should match
data is dummy data but main requirement is that column names of 2 excels should match
if doesn’t match then the data should not enter into resultant sheet
if it matches then the book2 excel file data should go into resultant file under same column
Can you try the below query:
dt3= (From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable() On row1.Field(Of String)("Invoice Number") Equals row2.Field(Of String)("Invoice Number")
Select row2).CopyToDataTable()
dt3 is of DataType Systrm.Data.DataTable()
Regards
1 Like
should I declare same for other columns also?
If Invoice Number matches it selections the row from dt2 datatable and write it in resultant sheet.
Regards
for other columns ??
Should I put this assign activity in for each row?
can you try my approach
It is an linq query. you need not use For each row you can directly assign it in assign activity.
Refer the below image.
In Write Range Workbook mention the cell as A2.
Main.xaml (8.1 KB)
Regards
=VLOOKUP(Sheet2!B1, Sheet1!A:B, 1, FALSE)
Explanation:
- VLOOKUP: This function searches for a value in the first column of a specified range and returns a value from the same row in a different column.
- Sheet2!B1: This references the first cell in Sheet2 column2, where the comparison value is found.
- Sheet1!A:B: This specifies the range of data in Sheet1 to be searched (column1 and column2).
- 1: This indicates that the result should be returned from the first column of the specified range.
- FALSE: This ensures an exact match is required for the condition to be met.
Its not handling the null values .There are so many blanks in the rows the blanks should also be handled
Can you try this below query:
dt3= (From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable() On If(String.IsNullOrEmpty(row1.Field(Of String)("Invoice Number")), String.Empty, row1.Field(Of String)("Invoice Number")) Equals
If(String.IsNullOrEmpty(row2.Field(Of String)("Invoice Number")), String.Empty, row2.Field(Of String)("Invoice Number"))
Select row2).CopyToDataTable()
Regards