Find umatched rows and highlight unmatched cells in those rows

I am having a situation where below shown datatable Sheet1 is matched with Sheet2 and the unmatched row of sheet1 should come in the Output sheet where the unmatched rows are highlighted.

Please help me to provide a solution of this situation where matching of row wise data to be done with column wise data.

The excel file is attached here

Sheet1 image

Sheet2 image

Output image

Hi @Swara_Soni thank you for all of the data and sample spreadsheets. So my apologies for asking this, but it’s still not clear to me what behavior you are trying to achieve with your automation?

@AndrewHall i want to achieve that from the two data tables first i want to get the rows of sheet1 which does not matched with sheet2 data and then colour those cells or columns which are the one’s who does not match .
For example in sheet1 row0 shows F T T F F T
and sheet2 shows F F F F T T
This shows that row0 is unmatched row and the unmatched cells are of column B C E.
So colour those columns (B,C,E).

@Palaniyappan Can you help…?

Hi @Swara_Soni this is a really interesting use case and definitely gives us some areas to make things easier. I created a sample workflow you can download (linked below) that does what your attempting based on my understanding.

What I understood was the value in the “RP” column will serve as the “key” to match on (i.e. you want to compare values of the columns in the rows from Sheet1 and Sheet2 where RP has the same value)? If that isn’t correct then my solution won’t be exactly what you want.

The other large caveat is we don’t support changing the color of cells at this time, so I added a * to the non-matching ones instead. Here is a link to the sample project

The basic approach is as follows:

  • Do a “For Each Excel Row” through Sheet1
  • Filter Sheet2 to show only rows where the RP column is equal to the value from Sheet1’s RP column
  • Copy the current row from Sheet1 and Sheet2 to a temporary sheet transposed as rows (rows are easier to deal with than columns as we don’t have a “for each column” at this time)
  • Do a For Each Row through the temporary sheet comparing each column. If the column doesn’t match, add a * to it
  • If any of the columns didn’t match, append this to the output sheet transposing back from rows into columns.

@AndrewHall Thanks for the reply …

When i am using the workflow in my excel sheet it is not generating the output i require and also when i put my shared excel sheet it is showing errors like:
image

The scenerio required is:

  1. I want to compare the rows of Sheet1 from Sheet2 on the basis of “RP” column and want to find:
    a. The rows of Sheet1 which is not in the order of Sheet2 (i.e. filter out rows of Sheet1 which has different values order from Sheet2) and then
    b. Highlight (or *) unmatched cells in those filtered out different rows(i.e. unmatched rows) from Sheet1 in the output sheet.

Here the image shows Sheet1, Sheet2 and required output sheet Output.

The mentioned * in the Output sheet column shows these are the unmatched cells in the unmatched rows of Sheet1.

Regarding the “Range does not exist errors” you will need to update what I gave you to work with your ranges, sheet names, etc. That error means that in the context of running that automation for the activity that is giving that error the Range from mine is entered wrong (e.g. wrong column names, sheet name, something like that)

With regards to also adding rows that aren’t present in the 2nd sheet, you’ll to add an extra condition, you’ll need to to this similar to how the “Cells Don’t Match” (activity 1.4.2.3 in my sample) worked

  • Right after the Filter Activity (1.4.3), add a “Save for Later” along the lines of “Matching Rows” and set it to 0.
  • As the first activity inside the “For Each Excel Row” on Sheet2, add a “Save for Later” and set “Matching Rows” to 1
  • After the “For Each Excel Row” over Sheet2 add an “If” set the condition to “Matching Rows equals 0” and then do an Append Range into your output sheet.
    image