How to Copy Data from Sorted Excel 2010 Table to Same Sorted Table in another Workbook
Posted on 2015-02-20
- I have two multi-worksheet Excel 2010 Workbooks, say mywb_rev1.xlsm and mywb_rev2.xlsm in use.
- both worksheets contain a large data table and are identical except for the comments a user will enter into one column
- rev1 is in use by a different user while I continue to to add features to rev2.
- The user enters some comments into one of the columns in mywb_rev1 and emails it back to me.
- Both rev 1 and rev 2 are sorted according to the same criteria (so, for the sake of illustration, let's pretend that this results in records 1, 20, 21, 22, 23 showing up as the top rows in the sorted table in each workbook)
- I'm trying to update rev 2 with the new information (in this case comments in one column) from rev 1
- If I simply try to copy and paste from rev 1 to rev 2, supposing I copy the comments in the first four rows of the sorted table in rev1 (which would be the comments in records 1, 20, 21, 22), when I try to paste them into rev 2, the comment from rev1/record 1 correctly goes into rev2/record 1 but the comment from rev1/record 20 incorrectly gets pasted into rev2/record 2 and so on (row21>row3, row22 >row4) even though both tables are showing the same sort order at the time of the copy and paste
- If i try to use a formula so show the equivalent of rev2CellX5 = rev1CellX5 (this isn't correct syntanx, it's just illustrative) with the idea of subsequently converting the values to text, the first record from rev1row1 gets applied to every cell in that column in the rev2 table
1. How can I copy and paste correctly from the table in the rev 1 workbook to the table in the rev 2 workbook?
2. Is there another way to sync the data between the two workbooks (I'm in the process of migrating all this to an Access DB but it will take some time to do this and I expect to run into this problem in the future with workbooks in use by different users)?