Compare 2 excel sheets and highlight which values are not matching.

[sabeoleonunes]

Hi all,
I need to create a flow where i compare 2 excel files  and check if they are same, if not same  changing the font color or making the font bigger of the non-matching values in the any of the one excel file would be helpful to identify which all values are not matching.

So here Microsoft is not matching with Google so just Highlight any of the one Microsoft or Google.

Like this.

[sumurthy · Accepted solution]

This scenario can be accomplished using "Run script" action on Excel online flow. It requires creation of 2 Office Scripts (TypeScript scripts from Excel for web). More details are here: 

https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration 

For your scenario, here's how I'd go about it - 

(Assuming you only want to do comparison in file-2. If you need to highlight in file-1, simply repeat steps by switching files)

1. Trigger > 

2. Write a script that returns the values from your worksheet/or table from File-1. Return unique values of companies. Run this as part of "run script action"

3. Pass that as input to second script, which then compares/looks-up against the input unique list of values being passed against File-2 and if not found will use the APIs to highlight the cell. 

// Script named: Return unique companies

// Run this against source file containing company names. Assumes Sheet1 and a table within it containing "Company" column. Change as per need. 

function main(workbook: ExcelScript.Workbook) {
    // Your code here
    let mySheet = workbook.getWorksheet('Sheet1');
    let table = mySheet.getTables()[0];
    let data2D = table.getColumnByName('Company').getRangeBetweenHeaderAndTotal().getValues();
    let companies = data2D.map(row => row[0]);
    
  let uniqueCompanies = companies.filter(function (item, pos) {
    return companies.indexOf(item) == pos;
  })
  console.log(uniqueCompanies.join('|'))
  return uniqueCompanies.join('|'); 
}

// Script named: Highlight missing companies

// Run this against target file where you want to highlight. Assumes Sheet1 and a table within it containing "Company" column. Change as per need. 

function main(workbook: ExcelScript.Workbook, uniqCompaniesStr: string) {
  uniqCompaniesStr = 'Microsoft|Google|Netflix|Apple'
  let uniqCompanies = uniqCompaniesStr.split('|');
  console.log(uniqCompanies);

  let mySheet = workbook.getWorksheet('Sheet8');

  let table = mySheet.getTables()[0];
  // First clear all formats
  table.getRange().clear(ExcelScript.ClearApplyTo.formats); 
  let dataRange = table.getColumnByName('Company').getRangeBetweenHeaderAndTotal();
  let companies = dataRange.getValues();
  for (let i=0; i< companies.length; i++) {    
    let row = companies[i]
    if (uniqCompanies.indexOf(row[0]) < 0) {
      // console.log("Not found: " + row[0] + " at: " + i)
      dataRange.getCell(i,0).getFormat().getFill().setColor('Yellow');
    }
  }
  return;  
}

Save above scripts in your Office Scripts and run this in Power Automate using "Run Script". 

PA flow with 2 scriptsExcel inputExcel outputExcel for web - create scripts

[sabeoleonunes]

Hi @sumurthy ,
Can you please post the script.

[sumurthy]

Added 2 scripts you will need. Let me know if you need help with using them in power automate.

[sabeoleonunes]

Hi @sumurthy 

Can you please help me to execute this in Power Automate.

[sumurthy]

Done, see above screenshots in the original response. 

If it works for you, please accept the answer. 

If it doesn't work, please email and we can discuss offline.

Quick note - if you don't see the "Automate" tab in Excel for web (see my screenshot), please have your O365 Admin turn on Office Scripts. See here for details: https://docs.microsoft.com/en-us/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365...

Within few weeks we should have it rolled out to all users (non GCC) by default.

[sabeoleonunes]

Hi @sumurthy ,

The above scripts worked completely fine, and I was able to get the non-matching values colored.
Just wanted to know , if there are more then then 2-3 columns to compare how will the script be ,

Excel input 1

Excel input 2

Excel Output

And do we always need to pass the input data in the script?

Thanks.

[sumurthy · Accepted solution]

Passing multiple columns may be easier if you return an object out of the 1st script. It'll help you with comparison in the next script. But the flow and logic will remain identical. 

[raash]

@sumurthy , is it possible to create a new file with the not matching values instead of highlighting them?