I will try to explain as simple as I can
So you have two DataTables (dt_sheet1, dt_sheet2) made from
Read Range activity. Then I make Sheet3 with headers to prepare it for data input. In first
For Each Row I’m going through each row of dt_sheet1. I need to compare Column A from sheet1 with column A from sheet2:
row.Item(0).ToString = dt_sheet2.Rows(dt_sheet1.Rows.IndexOf(row)).Item(0).ToString
row.Item(0) means first cell from from as indexing is starting from 0
then compare “=” so it will get only those element which are the same for both sheets
with first cell of particular row from sheet2 [dt_sheet2.Rows(…).Item(0)]
As we are going through each row of sheet1 we need somehow also iterate through each row of sheet2 otherwise we would compare each row of sheet1 with only the first row of sheet2. That’s why in brackets we are using trick to get actual index (integer) of row from sheet1 which is processed in this moment. [dt_sheet1.Rows.IndexOf(row)]
Next it reads range of sheet3 which at this moment will have headers and column A filled out.
In last 'For Each Row` activity I’m doing same trick but this time I’m comparing sheet3 elements with sheet2 elements in column A and then it writes corresponding elements from column B in sheet2 to column B in sheet3. So condition in this loop is the same as in first one with only difference that it’s sheet2 and sheet3.
The magic is done in “then” section:
Item from column B of sheet2 [dt_sheet2.Rows(…).Item(1)]
is written based on:
actual processed Index number (integer) of sheet3 row [dt_sheet3.Rows.IndexOf(row)]
Hope that at this point all is understandable. Cause you may ask how it knows to which cell all information needs to be provided. So in both loops in
Write Cell I’m using similar trick:
“A” + (dt_sheet1.Rows.IndexOf(row) + 2).ToString
“B” + (dt_sheet3.Rows.IndexOf(row) + 2).ToString
Which means that i will write data to column A (or B). I need to indicate number to have proper cordinates like A1, A2, A3 etc. So as a number I’m using actual processed index number (integer) of DataTable + 2. 2 because as you remember indexing is starting from 0. I need to skip default column name (A or B) and headers that’s why it’s +2.