How to keep same valued cells between 2 excel documents

Help
, , ,
1

Hey everyone.

I have 2 different excel documents, document A and document B, but they both have the same column named “Vendor Number”. Document A is the bigger document here it has more Vendor Numbers in it.

What I am trying to do is on the Document A i want to write “no match” on the vendor numbers that dont match between Document A and B.

for example.

Document A excel

Vender Number:
14
11
3
12
7
8

Document B:
Vendor Number:
5
6
3
14

The final version of A should be after the lookup

Document A excel

Vender Number:
14
11 no match
3
12 no match
7 no match
8 no match

How can i make this happen?

2

Hi,
Using Excel scope, Read doc A, in a datatable dt_A
Using Excel scope, Read DOC B, in a datatable dt_B
add a data column Status to dt_B , ie to store the match status

for each row in dt_A
assign temp= row(“vendor Number”)
for each row in dt_B
check using If statement whether row(“vendor Number”) ie B is equal to temp
if it equal write row(“Status”)= “match”

outside for loop write the dt_B to excel and get it as output.

Happy Automation!

3

@berkaykor
find starter help here:
FindMatches_1Col_LeftJoin-JDT_MatchResult.xaml (11.4 KB)

4

there are like 500 line on the excel, as i understood from your explanation, you are telling me to write for loop inside a for loop which i dont really want to do because it will take too much time then it should. Is there a way of doing this in a single execution.

5

thank you very much, this is actually what im looking for.

6

data table activities are much quicker, please give a try and look for the timings, if its feasible for you. Thanks

7

sure let me give it a go

8

hi, thanks for sharing your code, could you please explain more on the assign part, I have never done joints before, just keen to learn.image

9

@Saranyajk
in the join datatable activity we bring both datatable together by matching join column
the result in the corresponding join column from dtB will be empty if not present
for creating our result report we do use this LINQ

From d… - iterate over all dtJoin Rows
Let … check if in the name col value is empty (not joined) - set it to NoMatch or let it empty
Let ra…- construct an RowArray for the report table (NameX col, MatchResultInfo
Select… ad the ra to the result table and copy it to the result datatable

1 Like
10

thanks, but what is d?

11

@Saranyajk
it is like a local variable reference within the statement

have a look here:

1 Like
12

@ppr

I have used your code but I am having “Assign: Object reference not set to an instance of an object.” at the assign activity.

I have modified the code in terms of my excel so it is like.
(From d In dtJoin.AsEnumerable
Let result = If(String.IsNullOrEmpty(d(“SA sipariş numarası”).toString.trim), “NoMatch”,“”)
Let ra = {d(“Degisim No”),result}
Select dtResult.Rows.Add(ra)).CopyToDataTable

you have any clue why this error is happening?

13

@berkaykor
ensure that dtResult is of datatype Datatable, within the right scope and correct specified with its structures as done in the demo code. (refer to third build datatable)

14

@berkaykor

This was missing and not defined / not initialized in the XAML that you shared with me by messages.

the shared workflow (please check your messages) is using the Any Function approach as an alternate and updates the excel

dt1.AsEnumerable.Any(Function (r) r(0).toString.trim.Equals(row(0).toString.Trim))
and updates the B Col Cell depending of the check result

15

ty for the other solution you shared, it works but there is almost 5000 rows so it takes a long time an at somepoint excel gives an error no reason.

I still cannot implement the first solution you shared to the scenario I wrote in the message, the first solution was a single execution so thats actualy the type of solution i need since there are a lot of rows in the main excel.
I declared all the variable with the right scope sequence and data types, still couldnt make it work.
Again thank you for your interest…

16

@berkaykor

the first implementation can be combined with the datatable (this one with the B col) and the datatable with match info. It can be reconstructed in memory and then written out to excel.

17

how could i combine it?