How to search for Excels's values in Excels in excel

[Krithi1]

Hi,

I have a huge data in two excels.

Excel1 has six columns “A”, “B”, “C”, “D”, “E”.“F”

Excel2 has three columns “X”, “Y”, “Z”

I need to fetch all rows values one by one from column “D” from Excel1 and search for them in Excel2 “Column Y”

If the value is found in Excel2 “Column Y”, I need to get the corresponding cell value from “Column Z”

Depending on the type in “Column Z” I have to put them in one of the three data tables I created.

I can achieve this by using “For Each Row” and then doing one more “For Each Row” and using “If” statements.

But this approach takes a very long time because of the huge data, if we have to go row by row comparison between two excels. How can I achieve this more productively?

[postwick (Paul Ostwick)]

This is called a join. Use the Join Data Tables activity.

Read Range Excel1 into a datatable DT_One. Read Range Excel2 into a datatable DT_Two. Join Data Tables (set to Inner with “D” = “Y”). The output datatable will have all columns from both tables, but only the rows where D and Y matched.