Compare 2 excel sheets and get 3rd column value

Help Activities
, ,
1

Hi All…

I have 2 excel sheets which has different headers. in that i have to compare 1 column in each of the sheets. if any data matched i have to get the value from different column in the 2nd excel sheets and store it to the1st excel sheet.

example

Screenshot (115)
Screenshot (115)905×686 13 KB

I have to use linq. becoz sheet has multiple values.

please give me a solution
TIA! :):slight_smile:

2

Hi @soundarya.a
Are you want to get data from both sheet in match case or just sheet1

3

Get the data from sheet 2
Compare the column(sheet 1 → ColA, sheet 2 → ColF) from both sheet if matched get the values from another colum ( Sheet 2 → ColG) and write it to the sheet 1 → ColB

TIA :wink:

4

@soundarya.a ok I can try.

5

Thanks. :slight_smile:

6

Hi

In the above question how is 456 getting value def and 457 getting value efg, even when there is no matching data for these two?

Thanks

7

sorry i mistakenly i entered the data like that.

8

@soundarya.a
Please try this one.

DT2.AsEnumerable().ToList().ForEach(m=>{
DT1.AsEnumerable().Where(r=> m.Field(“ColF”)==r.Field(“ColF”)).ToList().ForEach(s=>{
s.SetField(“ColB”,m.Field(“ColG”));
});

9
  1. Use of the VLOOKUP Function to Get Result from a Third Column in Excel.
  2. INDEX+MATCH+IFERROR to Get Output from a Third Column in Excel.
  3. INDEX-MATCH Array Formula to Match Two Columns and Output from Third.

Regards,
Will

10

Hello @soundarya.a
Kindly refer to this XAML file, you may get some idea
Forum_Excel_CompareFilter.zip (92.8 KB)

11

Hi @soundarya.a ,

Have you tried using Join Datatables Activity ? Inner Join should be able to get your expected output, However, after the Join Activity you would need to use DefaultView.ToTable() method and preserve only the columns you require. Then perform the change of the column name if necessary.

12

Thanks for your help. But I am having a 2000+ Datas. So, I have to use linq

13

Have you tested the flow? Is it working right?

From my knowledge, Dt activities and Linq will take the same time.

14

Kindly find to this updated xaml file using the Linq expression
Forum_Excel_CompareFilter.zip (94.4 KB)

Dt2.AsEnumerable().Where(function(row) Dt1.AsEnumerable().Select(function(r) r.Field(Of double)("Column  1")).Any(function(x) x = row.Field(Of Double)("Column  2"))).CopyToDataTable()