I am having an automation use case where i need to compare column values of two different excel sheets .If there is a match highlight with green else highlight in yellow. in my case i have taken two excel in two different data tables. then use two for loops .inner and outer for loop but even if a matching value is found its going to the else part and highlighting everything in yellow. Can anyone please on this .Attached my workflow and excel SalesForce_OutputFields.xlsx (8.5 KB) pdf.xlsx (6.5 KB)
I want to compare the cell values individually. For example the value of quote number in two excels if match then highlight green else highlight yellow. Next compare the next column values of two excel sheets . If match highlight green. Next compare the third column value of two sheets . If match highlight green . Like this compare all the column values of the two sheets and if match highlight green else yellow.
Read Range activity to read the file salesforce → variable as dt_Salesforce
Read Range activity to read the file pdf - > variable as dt_pdf
Now use For Each row activity for dt_Salesforce
Declare all the variables columns as variables
Eg: Str QuoteNumber = CurrentRow(“QuoteNumber”).Tostring
Now inside the For Each Row activity keep a Filter Datatable activity and pass the Variable which you created and now Input Datatable is PDF and output Datatable is some dt_Temp
Place a If Condition as dt_Temp.Rows.Count > 0
Then you find a match in PDF and do you formatting
Else leave that so that It will pick the next record
At the end put clear Datatable activity and pass the dt_Temp variable to clear the contents
Thank You for your help .Actually i need to compare all the column values of two excel sheet and if the values are equal then mark that cell value as green. for the steps you ,mentioned will it compare cell values and mark green if match is found.
Yes, because you are taking all the variables and using in the filter of dt_pdf, so then you are checking if you are getting the rows means it is a match else it is not a match
I checked the above solution.But i want to compare the cell values of two excel sheets. If match then it should highlight the cell green. I saw in sheet 2 its fetching the column values of Salesforce but my usecase is to compare the cell values of column of two sheets .Thanks
Its showing green for first three fields and rest yellow but the output is not saved. I mean the highlighted cells of the excel are not getting saved . Do we need to use any save option for the output. Also I have doubts in few places . Can you please explain the workflow if possible .That would be great help. Kindly please let me know how o save the highlighted columns as output and please explain the following steps below what you are doing
Could you please check the workflow if its fine. I am attaching the workflow. Please let me know if this should be the workflow. Thanks Workflow.zip (2.2 KB)
i have unchecked the save option in use excel file activity you can just select so the after completion it will automatically saves the file
1 point ,as columns names are different btw 2 files and the order as well so i have created sheet2 with columns of both sheets to compare and reading that data to Datatable and converting to dictionary(of String String) so as to dynamically refer the column names , so in future if their is any slight changes in names also just by updating in excel sheet works (this you can keep any where and use )
2.in for each loop and iterating columns i am referring the each column(cell) to check from excel to data table from other file that the value is equal or not
3.for formatting the cell we have provide the cell reference (address) so using row properties