Hello, I’m trying to compare two excels (excel A and excel B ) if the value in Column A of Excel Ais the same as value in Colum A of Excel B then I want to replace or assign the corresponding value in Colum C of Excel B to value in Colum C of excel A… Here is what I did :
for each row in excel A
for each row in excel B
if columnA in excelA=columA in excelB (I already stored these 2 variables)
4.Then assign columC in excel B to Colum C in excel A
then I write the colum C in excel A on word file. The weird thing is that it only shows the first value of ColumC in excel B the rest matched does not come out at all
Later I tried to delete the 2 “for each” steps then all the value of Colum C in excel A has been changed to the first corresponding value of Colum C in excel B when excel A and B are matched .
This way it will not work properly. Here you have my workflow I have prepared for you. You have two excel sheets assigned to DataTable variables and then looping through each element of sheet1 I’m checking if it’s same for sheet2. If yes then “OK” is written, if no then value of sheet1 is written to column C of sheet2. Excel.zip (18.2 KB)
Based on this you should be able to work with your project.
Hello, Pablito thank you so much for the help. But this does not solve my problem. Sorry I didn’t make my question clear. Here I made a more clear example. in the excel there are 2 sheets and you can see there are 2 columns in each sheet. one is the code and the other one is the name for the code. what I need to do is to write down the codes that are the same in two sheets + coordinating names from sheet B. Can you please help me with this?
Hello @Pablito I’m sorry for the late reply and thank you so much for helping me. There is one thing that I don’t understand because I just got started which is “row.Item(0).ToString = dt_sheet2.Rows(dt_sheet1.Rows.IndexOf(row)).Item(0).ToString” can you please explain this?
Thank you so much !
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) 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.
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:
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.
jast1631
ashokkarale
Anil_G
lrtetala
Yoichi
SorenB
anjani_priya
ppr
Raj_esh
marina.dutta