How to compare two excel sheet & update the matched value in sheet 2

Help Studio
, , , , , ,
1

Hi all,

I have 2 worksheets, In sheet 1 it holds ID & Amount and in sheet 2 it has only ID.
I have to compare both sheet & If Id matched I want to get the amount and write in sheet 2,
example,
Worksheet A
ID Amount

1111 $123

2222 $678

3333 $980

4444 $650

Worksheet B
ID Amount
1111 $123

5555

3333 $980

0444

Thanks in advance

2

Hi @nithya,

Try this code.
You can test on more values by adding some more rows to Sheet1 and Sheet 2.
Input folder - It have input excel with 2 sheet.
Output Folder - It have output file

This is working code you can use it as it is.
LinqLeftAndRightJoin - temp.zip (16.5 KB)

Let me know if you face any issue.

1 Like
3

Hi @ermanoj3101 ,

Thanks for the reply. I dont want to write output in different excel file. I want to write amount in sheet 2.

example - sheet 1
ID Amount
001 $24

sheet 2
ID
005
001
003

Expected output in Sheet 2

ID Amount
005
001 $24
003

4

Hi @nithya ,

You can change the path wherever you want it to right.

5

Hi @nithya, Is it worked ?

6

Hi @ermanoj3101 ,

Yes, when I changed the path its working, but the order has been changed.It keeps matched item first and unmatched value last.

sheet 2
ID
005
001
003

Current output in Sheet 2

ID Amount
001 $24
005
003

Expected Output is
ID Amount
005
001 $24
003

Thanks in advance

7

ok, let me check again.

8

Try now,
LinqLeftAndRightJoin - temp.zip (10.1 KB)

9

Hi @nithya
You can try using invoke code activity too.
Steps to be followed

  1. Read the excels as dtA [For excel A] and dtB [For excel B]
  2. Now use invoke code activity

Arguments passed to invoke code [ added in order of Argument name, argument direction, datatype, value]

dtA— In— DataTable—dtA
dtB— In/Out— DataTable—dtB

Now use this code in code section

dtB.AsEnumerable().ToList().ForEach(Sub(r) r(“Amount”)=If(dtA.AsEnumerable().Where(Function(row) row(“ID”).ToString.Equals(r(“Amount”).ToString).Count<>0,dtA.AsEnumerable().Where(Function(row) row(“ID”).ToString.Equals(r(“ID”).ToString)(“Amount”).ToString,Nothing))

Regards,
Nived N
Happy Automation

1 Like
10

@nithya , Is it worked?

11

@ermanoj3101
Yes, It worked.

Thanks

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.