0

I have a user who has one spreadsheet with a bunch of data and sends the spreadsheet out to a client who will change some of the values.

The values changed will ONLY be in one row, but we don't know which rows were changed.

So, for a small example say I have one spreadsheet testData, with 5 rows and a column ACCEPT/FAIL and another one testData2 with the same number but I want to see if there is any rows that do not much in the ACCEPT/FAIL column -- I would like to highlight the rows.

I tried using this post for ideas, but I won't know how many rows there are...

Community
  • 1
Envin
  • 103

2 Answers2

1

You should be able to use conditional formatting where a formula will provide the criterion.

  • First step is to create a new worksheet in the original workbook, since conditional formatting does not allow criterion formulas to compare values in different workbooks.

  • Use this sheet to bring over the other spreadsheet's data with a simple ='[otherworkbook.xlsx]Sheet1'!A1 reference (no $ signs).

  • Copy this formula down and across to get the rows and columns of data in the other workbook (or limit the the formulas to the specific column or columns you are interested in comparing).

Then, you can set the conditional format:

  • In the original worksheet, select the data range you want to compare, such as the Accept/Fail column), or the whole range if you want the rows in which a difference exists to be highlighted.

  • Select Conditional Formatting on the Home ribbon, and New Rule and Use a formula to determine which cells to format from the boxes that pop up.

  • Enter a formula something like =$F1<>Sheet2!$F1 in the input box labeled "Format values where this formula is true" (assuming F1 is the column you are interested in). The dollar signs shown here anchor the column but not the rows - if you want a cell-by-cell comparison over several columns, drop the $ completely.

  • Set the format to highlight the cells where the values are not equal in the two sheets.

Trickiest part of this is getting the dollar signs right, at least for me it is!

chuff
  • 3,534
0

As a quickie, I'd export both files to CSV and do a quick diff, say use Windiff or WinMerge, on the csv files.

Rich Homolka
  • 32,350