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)
Can you elaborate
you want compare the cells individually respective to column names with ref to Quote Number
I mean if the quote number is same then it has to compare every column value and highlight the cell
which is do you need to compare ie which is base file to compare
As column names in both files are different on what criteria need to compare
Hi Lakshmi Narayan,
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.
Column names for both files are different either having some spaces , some part of the name is different
is it going to same every time so that to hard code rather than doing dynamically
Example QuoteNumber from Sales file and Quote Number from pdf file may be same but different having space
Legal_AccountName from Sales file and Customer legal name from pdf file is not matching so in these the option is hard coding only
Hi Lakshmi ,
Column names will be same .
Thanks & Regards
Formating.zip (18.2 KB)
i have crated column names in sheet2 for reference
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
Hope this may help you
Do you have an example workflow for this. That would be helpful to understand. Thanks .
Have you checked the above solution
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
Do we need two for each loop . One for dt_Salesforce and the other dt_pdf.
did you run ?
it will compare with respective cells from one file to other file
No two loops, Only one loop for dt_Salesforce
Inside that loop only we are using the Filter Datatable and pass the variable values to dt_pdf
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
1)dt_ColumnNames.AsEnumerable.ToDictionary(Function(r) r(0).ToString,Function(r) r(1).ToString)
2)CurrentRow.ByField(columnDisc(column.ColumnName)).ToString = dt_PDF.Rows(CurrentIndex-1).Item(column.ColumnName).ToString
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
If you still have info let me know
I have still some questions. Let me know when can I connect with you for a meeting. I wanted to show the workflow where i am having doubts and errors.
sure , ping me when you are free , if possible can connect same time