Beginner needs help copy paste and find the right place

Help Studio
1

I have to sheets , one has "titles " in column B and is missing “text” in column C
the other has titles in A and the text in B
I need the tool to find the right text to the right tittles since they are not in the same order

2

Hey @gerdonline

  1. Use read range to read the entire sheet and save output to variable dtSheet1.

  2. Next, use read range for the second sheet and save output to variable dtSheet2.

  3. Use For Each Row activity to iterate through dtSheet1
    Inside this loop, use another For Each Row activity to iterate through dtSheet2
    Use if activity to compare the titles: currentRow1("titles").ToString = currentRow2("titles").ToString
    Then: Assign the matching text to dtSheet1: currentRow1("text") = currentRow2("text")

  4. After updating dtSheet1 with the missing texts, write it back to the first Excel sheet or a new sheet used Write Range activity.

Alternatively you can use (assign to datatable variable):

(From row1 In dtSheet1.AsEnumerable()
Join row2 In dtSheet2.AsEnumerable()
On row1.Field(Of String)("titles") Equals row2.Field(Of String)("titles")
Select dtSheet1.LoadDataRow(New Object() {
    row1("titles"), 
    row2("text") 
}, False)).CopyToDataTable()
3

I highly suggest doing the free training on the academy.uipath.com web site. That way you’ll understand basic things like how to work with Excel files. For example…

4

Hi,

To match and copy the text to the corresponding titles between two sheets where the titles are not in the same order, you can use the VLOOKUP function in Excel or the Lookup Data Table activity in UiPath. Here’s how you can do it in both:

Using Excel VLOOKUP function:

  1. Open your Excel workbook with both sheets.
  2. Go to the sheet where the column B has titles and column C is missing the text.
  3. Click on the first cell in column C where you want to paste the text.
  4. Enter the following VLOOKUP formula:

=VLOOKUP(B2, Sheet2!A:B, 2, FALSE)

  1. Here B2 is the reference to the title in the current sheet. Sheet2!A:B should be replaced with the actual name of your other sheet and the range where titles and texts are located. 2 is the column index in the range where the text is found (B column in the second sheet), and FALSE specifies that you want an exact match.
  2. Press Enter, and if there’s a matching title in the other sheet, the corresponding text will appear.
  3. Drag the fill handle (a small square at the bottom right corner of the cell) down to fill the formula for the other cells in column C.

Using UiPath with Lookup Data Table activity:

  1. Use the Excel Application Scope activity to open the Excel file.
  2. Use Read Range activities to read the data from both sheets into DataTable variables, let’s say dtTitlesWithText for the sheet with complete data and dtTitlesMissingText for the sheet with missing texts.
  3. Use a For Each Row activity to iterate through dtTitlesMissingText.
  4. Inside the loop, use the Lookup Data Table activity:
  • For the DataTable property, use dtTitlesWithText.
  • For the LookupValue property, use row("titles").ToString, assuming “titles” is the column name for titles.
  • For the TargetColumn property, use the column index or name where the text is stored in dtTitlesWithText.
  • For the ColumnName or ColumnIndex property, use the index or name of the column with titles in dtTitlesWithText.
  • For the Output property, create a variable to store the found text.
  1. Use an Assign activity to set the value in the dtTitlesMissingText DataTable with the text found by the Lookup Data Table activity.
  2. After the loop, use a Write Range activity to write the updated dtTitlesMissingText back to the Excel sheet.

Remember to use proper exception handling in UiPath to manage cases where a matching title is not found. The VLOOKUP method in Excel is straightforward and suitable for a quick task.

Thanks

5

Thank you Piotr, much I need learn. :+1:

6

Thank you all of you , will take the time and report when done

7

vlookup didn’t work because the spelling is to different (sometimes with capital letters , sometimes in ")