0

I have to manage "Change Control" on Excel spreadsheets comprised of 1000's of rows and dozens of columns.

Downstream Teams often submit revised columns that I have to incorporate into a master version, but they do not always indicate where they have changed cell values from the previous version. Is there a function that will create say a 4 digit hash value across all cells in a row (both numeric and text) ?

If so I could use that to compare the new spreadsheet Row hash value to previous spreadsheet version hash value for same row and then highlight rows that have incorporated any change over the previous version.

I can't use VBA or macros as these are frowned upon in my organisation. Your help appreciated.

p.s. The hash value can be anything as long as it uses all contents of all cells in a row to derive itself.

  • I understand why you might want to avoid VBA in production versions of the documents, but why not use VBA that you import and use only on copies of the document intended for your investigation? Once you gather the information you need, you close without saving, and then work on the official files with no macros or VBA. – Nick Russo Oct 7 '14 at 21:05
  • An alternate approach to accomplish the same result would be to use a template worksheet, where every cell compares the associated cell in the new spreadsheet to the one in the original. If they don't match, have it display the cell reference. Then you look at a blank worksheet populated with flags and locations. You can also use those values to create a row by row summary. – fixer1234 Oct 7 '14 at 21:28
  • 1
    Can you clarify why you are not using the "Track Changes" feature in Excel? office.microsoft.com/en-us/excel-help/… – Sun Oct 7 '14 at 22:10
0

Two things come to mind:

1) Create a new excel document

2) Copy today's data into one worksheet

3) Tomorrow copy tomorrow's data into another worksheet

4) Create another worksheet, and either:

4.1) Put in a formula like if

=IF(Sheet1!A1<>Sheet2!A1,1,"")

then drag that down to the bottom of the page, and select all those and drag them over to the right of the page.

This will show you which cells have changed.

4.2) The other option would be to use conditional formatting. You can put in a rule that will compare one sheet to another sheet and highlight in the new sheet what has changed

... though this might be a bit harder to find if you have so much data, where as the formula approach will let you do sum's of rows to narrow things down a bit.

Taking the formula idea a step further:

Below code would print the cell column/row in the column/row that does not match... so you could take copy and paste all the values out, and i'm thinking maybe remove duplicate values to get right of the spaces? or maybe sort all the values so you have them all together?

=IF(Sheet1!A1<>Sheet2!A1,SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(B1),"") &ROW(),"")
0

Can they buy you an Excel comparison software that costs $16? If so, use Excel Compare. The requirements are too restrictive for a good solution using Excel alone (no VBA).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.