Using:
Excel 2010
Background:
- 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
Problem:
- 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
Questions:
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)?
With filtered, data copy and paste works differently.
Copy a single cell and paste into a filtered range, the single cell value will populate only visible rows.
Copy multiple non-contiguous cells and paste into a filtered or non-filtered range and it will paste as a contiguous range.
As a way round, in the destination sheet with filter in place, add a formula into a spare column utilising a MATCH or LOOKUP function to pull the update from rev2. Put this into a single cell and then copy and paste or fill down for the filtered range. Then remove/disable the filter and the formula will only be in those cells that were visible. You can now copy the column and use paste special > values > Skip Blanks to put those new values into the correct column. The skip blanks is a tick box at the bottom of the paste special window.
Thanks
Rob H