I want to ask about how to retrieve the data from another sheet in one excel but the key data is contained in another sheet.
For example i have 1 excel contains 2 sheet.
The Column “NO” in the sheet 1 is the key data
This is sheet 2
As displayed the column “NO” is also in sheet 2 but with different number
The same is 100001 until 100003.
So my logic is if the column “NO” between the 2 sheet is same then the data that i want is column “price” in sheet2
is it need to build the data table between the 2 sheet and then using if function?
I hope you can understand my question. Thanks a lot
First Read the Excel sheet 1 and store it in a dt1
Then Read the Excel Sheet 2 and store it in dt2
Now Take the list of strings ListA
ListA= (From p in dt1.Select Select Convert.ToString(p.Item("NO"))).ToList.Intersect((From q in dt2.Select Select Convert.ToString(q.Item("NO")))).ToList
List A is the list of Column NO values which are there in both sheets.
Now take the List of Strings ListB
List B=(From p in dt2.Select
Where ListA.Contains(p.Item("NO").ToString)
Select Convert.ToString(p.Item("Price"))).ToList
List B is the values of Price in sheet2 Corresponding to the common values of ListA
Finally List B will contains the Output Result .Now you can write this List of Strings in an excel sheet.