Check if the values from one column in excel workbook exist in another column in other excel workbook

excel

#1

Hi,

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.

Workbook (i)

Workbook (ii)

Thank you.


#2

@Katyy1 use for each row and get values from first excel sheet compare with cross check file values


#3

Hi @Manjuts90,

Would you mind to elaborate further? Thanks.


#4

@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


#5

@Katyy1

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

Regards,
Mahesh


#6

Thank you @MAHESH1!

However, it throws some errors as shown below. Could you please advise?

image

I’ve also uploaded my workflow here. DatatableMatch.xaml (14.1 KB)


#7

Hi @Katyy1,
You can’t use “for each” for a String
Variable Type is String[] or List , not only String

Regards


#8

Hi @Chinh_Le,

I’ve made some adjustment on my workflow. But it still throwing errors. Could you please have a look and advise?

Thanks.

DatatableMatch.xaml (14.6 KB)


#9

@Katyy1
Try this nowDatatableMatch.xaml (14.9 KB)

Regards,
Mahesh


#10

Thank you, @MAHESH1.

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.

Thanks for your help.

DatatableMatch (1).xaml (17.2 KB)


#11

@Katyy1

If the count is greater than zero then get the the index of row and use set Range color activity to highlight the cell.

Regards,
Mahesh


#12

@Katyy1 ,

I hope there help you

DatatableMatch.xaml (22.9 KB)
DatatableMatch.xaml (22.4 KB)


#13

Thanks guys for your help it worked well.