Compare 2 excel sheet, highlight and add a status column(match/mismatch) beside each row

[GhassSD]

Hi all,

How to compare 2 excel sheets (3 columns to compare), highlight and add a status column(match/mismatch) beside each row ?

Thank you for your help!!

[Chriddle]

Can you explain what the comparison looks like?

[GhassSD]

I have two excel spreadsheets each sheet has multiple columns, but the common columns are 4: customer name, product name, quantity, and unit cost. I ask to compare these two sheets base on these common columns, where we have the same customer name and product name and quantity and unit cost, add a status (match/mismatch) field next to the row on any sheet and highlight the row. 

[Chriddle]

Do both files have the exact same number of rows in the same order?

Or can there be a data record in one file that is missing in the other?

[GhassSD]

No it's not the same number of rows and columns are different, we just have the columns listed above as common.

[Chriddle]

The general idea is to get a json array from both tables (I called them Table0 and Table1), each with only the comparable values (Step 1 to 4).

Then check if array1 contains a certain json from array0 (Step 5)

I don't know a way to add a column to a table, that's why I create a new table (Step 6)

Then iterate over the values from the Select that has the status (Step 8 )

and add a new row with all columns from Table0 (Step 9-10, that's why you need the index variable) and the status.

1. List rows present in Table0
2. Select action to extract common columns from Table0
3. List rows present in Table1
4. Select action to extract common columns from Table1
5. Select action with input from Step 2 and the new mapping "status"  to contains(body('Select_Table1'), item())
6. Create a new table in Excel with all columns from Table 0 plus an extra column "status"
7. initialize variable "index" with 0
8. Apply to each with input body('Select_Table0_status')
9. Add a row to the new table
10. Increment index

This is what the row in Step 9 looks like: 

{
 "customer name": @{outputs('List_rows_present_in_a_table_Table0')?['body/value']?[variables('index')]?['customer name']},
"product name": @{outputs('List_rows_present_in_a_table_Table0')?['body/value']?[variables('index')]?['product name']},
  ... all the columns of your original Table0 in correct order...
  "status": @{item()?['status']}
}

The flow:

My to input tables:

Result:

[GhassSD]

Thank you a lot @Chriddle for your help, is there a way to highlight the rows based on their status? 

[Chriddle]

I don't think you can do that with Power Automate directly.

However, you can format cells in an Excel spreadsheet based on their values.

If you want to do that you need to create this excel using a template or there might be a way to do it using office scripts.

[MichelleRanMSFT]

You can definitely automate this with an Office Script! I've tested out the following script using the input tables you gave, and it seems to produce your desired result:

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable('FirstTable');
    let table2 = workbook.getTable('SecondTable');

    // Create a new worksheet
    let sheet = workbook.addWorksheet();

    // Create new table
    let address = table1.getRange().getAddress();
    let addressWithoutSheet = address.substring(address.lastIndexOf('!') + 1);
    let newTable = sheet.addTable(addressWithoutSheet, true);

    // Copy the values of table 1
    newTable.getRange().setValues(table1.getRange().getValues());

    // Add a new column
    let statusColumn = newTable.addColumn(null, null, 'Status');
    let statusColumnIndex = statusColumn.getIndex();

    let table1Columns = table1.getColumns();
    let table2Columns = table2.getColumns();
    // Filter down to common columns & get the indices
    let commonColIndices = table1Columns
        .filter(col => table2Columns.find(col2 => col2.getName() == col.getName()))
        .map(col => col.getIndex());

    // Loop over each row in table 1
    let table1Rows = table1.getRangeBetweenHeaderAndTotal();
    let table2Rows = table2.getRangeBetweenHeaderAndTotal();
    for (let rowIndex = 0; rowIndex < table1Rows.getRowCount(); rowIndex++) {
        // Compare the values in table 1 to table 2
        let sameValues = true;
        commonColIndices.forEach(col => {
            sameValues = sameValues 
                && table1Rows.getCell(rowIndex, col).getValue() == table2Rows.getCell(rowIndex, col).getValue();
        });

        // Set status
        newTable.getRangeBetweenHeaderAndTotal()
            .getCell(rowIndex, statusColumnIndex)
            .setValue(sameValues);
    }

    // Format the status column to highlight FALSE cells in red
    let conditionalFormatting = statusColumn.getRangeBetweenHeaderAndTotal().addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
    conditionalFormatting.getCellValue().setRule({ operator: ExcelScript.ConditionalCellValueOperator.equalTo, formula1: "=FALSE" });
    conditionalFormatting.getCellValue().getFormat().getFill().setColor("#ffc7ce");
    conditionalFormatting.getCellValue().getFormat().getFont().setColor("#9c0006");
    conditionalFormatting.setStopIfTrue(false);
    conditionalFormatting.setPriority(0);
}

You'll need to replace "FirstTable" and "SecondTable" with your actual table names. I realize this is quite a lengthy script so if you have questions about a specific part, I'm happy to explain in further detail. Hopefully this helps!