Compare 2 excel sheets and populate matching column with value

Help Activities
, ,
1

Hi devs, I am comparing 2 excel sheets if ref column value in excel 1 is same with ref column value in excel 2. populate column k with the respective amounts

2

Hi,

Use read range to read excels. store into DT1 and DT2

then use join data table activity. Use Left join.

Pls mark this as solution if it resolves your issue.

Thanks

3 Likes
3

It’s not the same column

1 Like
4

@stanstilo

Follow the steps

  1. Read excel 1 into Datatable
  2. Use a for each row in datatable actvitiy
  3. Inside that use find/Replace and find for value currentrow(“RefColumn”).ToString
  4. If value found …then use write cell to write the amoutn into respective column as you already have the row number

cheers

1 Like
5

HERE IS FILE AND EXPECTED OUTPUT
Excel 1
image
Excel 2
image

EXPECTED RESULT
image

6

@stanstilo

Use join datatable and join on Name…

image

Then use filterdatatable to get the required columns only
image

Hope this helps

cheers

1 Like
7

We’ll populate “amount” with “cleared bill column” as seen in table. The inital excel 1 had empty amount but in the result table amount was populated with value

8

@stanstilo

After this use for loop on the reultant datatable

And inside use

Currentrow(“Amount”) = currentrow(“Cleared bill amt”)

Cheers

9

Hi Anil_G, I have used join datatable but the output table is having duplicate rows for the join, what could have caused it or have you experienced such jusing join before?

10

@stanstilo

Did you do inner join only? And are there repeated names?

Cheers

11

Hi @stanstilo

  1. Read Excel1 as DT1
  2. Read Excel2 as DT2
  3. Create a Build Data table with your required column names - Dtoutput
  4. Use for each row (include inside this for each row)

3.1. for each excel2row in dt2
3.2 another for each excel1row in dt1
3.3 Compare if excelrow1(“Name”) = excelrow2(“Name”)
3.4 Add datarow in the build DT with your required columns

  1. You will get the required rows in DToutput
    CompareExcels.zip (4.3 KB)

image
image809Ă—685 24.6 KB

Thanks