Compare excel and color match

I have a excel file with two sheets. Sheet A(Table 1) and Sheet B(Sheet1). My goal is to go through the all invoice numbers in column E of sheet B and match them up with the invoice numbers in column A of sheet A. If the robot finds a match, that invoice number/cell in sheet B has to be colored filled. I have attached a picture of what I have so far. Some suggestions or a code example would be awsome.

Thanks

@NATHAN_MORA

For Second for each row activity declare output variable(rowindex) which gives row index.

In then part, use set range color activity inside excel application scope.

select the required in property, sheet name and in range property select the column

for example Column C - then range should be “C”+(rowindex+2).toString

i have given +2 here because of array starts with zero index, excel starts with starting cell with 1(example A1,B1…) and first row in excel will be headers.

1 Like

Manjuts
Got this to work, any suggestion on how to also color the cells that don’t match?

@NATHAN_MORA Use same set range color activity activity in else part of if activity also but give different color.

Manjusts,
I’m working on the project we talked about above. I have the robot matching up the invoice numbers from sheet 2 with sheet 1. Once those numbers are matched the cell color is yellow. I have another phase that i could use some help.

when the robot finds the invoice number match from sheet 1 to sheet 2 the robot then needs to match the company name with the invoice number. Invoice number = column E and company name = column D.

If the robot finds the matching invoice number then it needs to check to see if company name also matches before moving on to the next invoice number. Any suggestions would be great or a work flow i can send you a test sheet if you need one. Below is a small sample. The invoice number and company from sheet 2 need to match invoice number and company name of sheet 1 then they both need to be color yellow before moving to the next line .

Invoice number Company
12345 pizza

@NATHAN_MORA Try below code same if activity, but change the column names accordingly

row.item(“Company”).ToString.Trim=line.item(“STMT”).ToString.Trim and row.item(“InvoiceNoColName”).ToString.Trim=line.item(“InvoiceNoColName”).ToString.Trim