Lookup using two columns in excel

Help Activities
,
1

Hi,

I have two excel tabs. sheet1 and sheet2.

I need to look for data in two different columns in sheet1 and validate if those two columns data exists in sheet2. If the data matches for each row, I need to update a different column with “Y” in sheet2 for each row. How can achive this?

Ty in advance!!

2

@Krithi1

  1. Read the sheet1 into datatable dt
  2. Use excel file with the excel and use for each row in excel on sheet2
  3. Inside loop use filter datatable on dt on columns you need in sheet1 …for value use currentrow.ByField("ColumnNameinsheet2").StringValue and save in filtereddt
  4. Use if condition with filtereddt.RowCount>0
  5. On then side use assign with currenTow.ByField("Sheet2ycolumns").StringValeu = "Y"

Cheers

3

Hello @Krithi1

Sequence
Excel Application Scope (Read Range for both Sheet1 and Sheet2)
Read Range: “Sheet1” → Output DataTable: dtSheet1
Read Range: “Sheet2” → Output DataTable: dtSheet2

 For Each Row: row In dtSheet2
 Assign: searchString = row("ColumnA").ToString & row("ColumnB").ToString
 Assign: matchingRows = dtSheet1.Select("ColumnA & ColumnB = '" & searchString & "'")

 If: matchingRows.Length > 0
Assign: row("NewColumn") = "Y"

End 

Write Range: "Sheet2" → Input DataTable: dtSheet2

Thanks & Cheers!!!
Log Message: “Matching rows updated in Sheet2.”

4

Hi

Use the “Read Range” activity to read the data from sheet1 into a DataTable named “dtSheet1”.
Use another “Read Range” activity to read the data from sheet2 into a DataTable named “dtSheet2”.

Add a “For Each Row” activity and connect the “dtSheet1” DataTable to the “Collection” property.

Inside the “For Each Row” activity, add another “Join” activity.
Connect the “CurrentRow” property from the “For Each Row” activity to the “Left” property of the “Join” activity.
Connect the “dtSheet2” DataTable to the “Right” property of the “Join” activity.
Set the “Join Type” property to “Inner” and the “On” property to the matching columns (e.g., “Column1” and “Column2”).
Add a “Filter DataTable” activity and connect the output of the “Join” activity to the “DataTable” property.
Set the “Filter Expression” property to “Column3 <> ‘Y’”.
Add an “Update Range” activity and connect the output of the “Filter DataTable” activity to the “DataTable” property.
Set the “Sheet Name” property to “sheet2”.
Set the “Range” property to the location of the “Column3” column (e.g., “B2:B100”).
Add a “Write Cell” activity and connect the “Column3” column from the filtered DataTable to the “Value” property.
Set the “Row” property to “RowNumber” and the “Column” property to “ColumnIndex”.