0

I have a very big shared Excel spreadsheet with changes made in some of the columns. I need to create a new spreadsheet which pulls from that big spreadsheet only the rows containing cells that have been changed - in other words all rows' numbers marked as read by Excel.

Very important thing is that all the changes still have to be VISIBLE (new and old values). Do you have any idea how to do it?

I am sorry if this question sounds stupid to you but I am a complete amateur and I need something to start with...

Community
  • 1
  • 1
AnaAna
  • 1
  • 1
  • This is not something that is particularly easy to do in excel. There is no track changes like in word. The only way to do this is to save a copy then compare workbooks. – Kharoof Dec 27 '15 at 22:47
  • There is a Track Changes in Excel on the Review tab. Otherwise, you will need to use the worksheet change event, but very involved... – WhiteHat Dec 27 '15 at 23:12
  • I guess you can try to use OnEntry or Worksheet_Change. These two links could be helpful to you: [First one](http://stackoverflow.com/questions/20053891/onentry-vba-function) and [Second one](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640). – Michele Dec 27 '15 at 23:33

2 Answers2

0

You can Track Changes on MS Excel. But there are some rules for that.

  • It is available only in Shared Workbooks.
  • Workbooks that has tables CAN NOT be shared.
  • To share a workbook you need to disable some privacy settings

Go to: File-> Excel Options -> Trust Center (again Trust Center button)

Under the Privacy Options category, under the Document-specific settings, uncheck the "Remove personal information from file properties on save"

Then Click OK

Now, you can share your workbook under Review Tab, Changes group. When you click Share Workbook command, it asks you some rules for sharing and change tracking. Choose properties which fits you then click OK.

Your workbook is now shared and you can easily Track Changes under Review Tab, Track Changes method group.

You may highlight the changes on screen and you may see changes on the history window. You may also Accept/Reject the changes.

Don't forget: Workbooks that have tables can not be shared. You can Convert To Range your tables before start sharing under Table Tools/Design backstage tab.

degergio
  • 81
  • 1
  • 11
  • I know how to use Track Changes tool but it does not provide the solution. My problem is that I have to show my coworkers only the rows of the spreadsheet that have been changed somehow (with all the columns being in a given row). So from thousands of rows I need to show them just 100 of them in a separate spreadsheet - without those remaining the same. Comparing workbooks might work in a way - but I must admit it is far too complicated for me to achieve a desired result with this method. – AnaAna Dec 28 '15 at 18:12
0

I think your simplest option is to manually 'version control' your files by saving your versions under a different filename (_v1.xlsb, _v2.xlsb etc).

You could then use Microsoft's Spreadsheet Compare tool (https://support.office.com/en-gb/article/Basic-tasks-in-Spreadsheet-Compare-f2b20af8-a6d3-4780-8011-f15b3229f5d8) to diff any two versions and see the modified rows.

Another solution would be to use a commercial solution. I know that sharepoint (https://support.office.com/en-us/article/Track-versions-of-a-file-in-a-SharePoint-library-54578c95-0538-407a-bc05-ea34d614f9e4) do document revision tracking (though I am not sure how much they give you in terms of diffing).

I have been working on a solution to this problem (www.pathio.com) where revisions of an Excel workbook get versioned in the background and an audit log with diffs is generated. I've put together an example of a diff here: https://app.pathio.com/bjoernstiel/Cashflow.xlsb/diff/sheets/Sheet1?version=c079909def1716c833e431e3a9ca1d1b2f5705d0

Bjoern Stiel
  • 3,918
  • 1
  • 21
  • 19