Compare two excel files and change the certain data from one to another

Help
1

Hi guys,

I have a use case like having two excel files say

  1. One.xls
  2. two.xls
  3. result.xlsx (In the Bottom) for reference |attachment](upload://q4Coiprl2dXmMSe6IfiEaxpVmBe.xlsx) (8.4 KB) Two.xlsx (8.4 KB) one.xlsx (8.4 KB)

i need to find the row which having set-text(column 3) in 1st excel and get the source (column2) for that text column that contain “Set-Text” and i have to compare that source (column2)name in 2 excel and respective field(Column 3) from the 2nd excel should replace the field (column 3) in the 1st excel.

I have attached the excel files of input and output both.

Help me to solve this …

Thanks in Advance…

regards
sriramresult.xlsx (8.4 KB)

2

hi @Sriram07

I got your flow, to achieve the same, you should follow steps below:

  1. Read Range :- one.xlsx —> dt1 (DatatTable)
  2. Use Filter Data Table :- “fields” = “Set-text” (to get only Set-text data ) —> dtSet_Text
  3. Read Range :- two.xlsx —> dt2 (DatatTable)
  4. For each row :- row In dtSet_Text (to iterate loop through filtered data)
  5. In that for each row take Lookup Data Table
    Input: 1. DataTable as dt2 —> as we’ve to search value in dt2
    2. LookupValue should be - row(“source”).ToString —> to search in dt2 with value of source column which has “Field” as ‘Set-text’.
    Output : make output variables for CellValue (to get value of “field”) and RowIndex (to get row index)
    Target Column: should be “fields” as we want value in “field” column.
  6. Write cell activity to put those values in excel one.xlsx accordingly.

I’m attaching my workflow for this, so you’ll get clear idea.
workflow : Lookup.zip (24.0 KB)

I hope this will help you. :slight_smile: