How to retrieve data from the selected sheet but its key data is contained in another sheet

Help
1

Hi All,

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.
6
The Column “NO” in the sheet 1 is the key data
This is sheet 2
7
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 :slight_smile:

Regards,

Delf

2

@delf

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.

Regards
Mahesh

3

Hi @MAHESH1

one quick question. What type of variable i use for list a and list b?

Thanks a lot

Regards

Delf

4

@delf
List of strings

Regards,
Mahesh

5

Hi @MAHESH1

I’m encountering an error. Can you give some example xaml? Maybe i’m doing wrong

Thanks

Regards

Delf

6

Hi @MAHESH1

Actually i found the error and already fixed it. Your solution works very well for my case.

Thank you very much :smiley:

Best Regards

Delf