There are 2 workbooks, (i) contain multiple spreadsheets, every spreadsheet contain one country’s database which gets updated monthly and (ii) database for crosschecking.
How can I check if the values from column C, Brand of Germany spreadsheet in workbook (i) exist in another column B, Brand (has to look for Germany in Column A) in workbook (ii). If the value doesn’t exists in workbook (ii), highlight the value in workbook (i). Same goes to other countries spreadsheets.
@Katyy1 read data from 1st excel with respect to one country and store it in a dataTable compare country and brand in second 2nd excel. For fetching values u can use for each row loop on both excel
Use Excel Application Scope and give the path of the work book, and store the workbook in a variable wb
Read the Workbook 2 and store it in a datatable dta
Then create list of strings List A
Use the below syntax in Assign Activity. List A= wb.GetSheets()
Note : Now All the below actions should be done in Main Foreach of ListA
Now run one for Each to List A
Let us take each value as item in for Each
Inside for Each use Read Range Activity, Mention Sheet Name as item and store it in a datatable dtb
Now Create a List of strings List B
ListB= (From p In dtb.Select
Select p.Item("Brand").ToString).ToList
Now Create a List of strings List C
Listc= (From p In dta.Select
where p.Item("Country").ToString.Equals(item)
Select p.Item("Brand").ToString).ToList
Here item is the Country name mentioned as sheet name in Workbook 1
Now Create a List of strings ListD
List D= ListB.Except(ListC) List D will contain the brands which are not there in work book 2 of respective country.
Now check the count of List D if it is greater than 0 then run the for each row and hightlight the value
I can now run the workflow. However, the output of the ListD is [system.string]. Also, could you please elaborate further on the last part, to check the count of List D if it is greater than 0 then run the for each row and hightlight the value.