Can anyone help me with the "Assign" problem

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 :

  1. for each row in excel A
  2. for each row in excel B
  3. 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 .

Can anyone please help me with this ? Thanks

Hi @StevenCM,
Welcome to the Community!

I think at this place something might be wrong. Can you show this part in your workflow?

@Pablito Thank you for replying
Here is what I did code and dcode refer to Colum A. nameofelements and dnames refer to Colum C
Thank you so much

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. (18.2 KB)
Based on this you should be able to work with your project.

Thank you so much for this! I’ll have a look tomorrow !!!

1 Like


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?

Here you go: (18.3 KB)

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 :slight_smile:
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.

Uff :smiley:

@Pablito I understand it now ! Thank you so much for the explanation! it really makes it much more clear to me ! Thanks !

1 Like