Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Solved
How to Copy Data from Sorted Excel 2010 Table to Same Sorted Table in another Workbook
Posted on 2015-02-20
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)?
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)?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
3
9 Comments
Active today
I take it as well as Sorting the data, it is also filtered with some rows hidden?
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
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
Active today
In answer to your question:
- If i try to use a formula so show the equivalent of rev2CellX5 = rev1CellX5
I suspect when you are creating the formula, because it is between different workbooks, the formula is ending up with an absolute reference:
=[Rev2.xlsx]Sheet1!$X$5
This is default behaviour for creating links between workbooks. When copied down the $5 stays the same rather than extending down. Once you have created one formula eg for row 5 as above, amend the formula to remove the $ from in front of the row reference. This will then adjust as you copy down. If you are filling down while filtered, disable the filter before copying to the correct column as mentioned in previous comment.
Other option is that you have Manual Calculation set, press F9 to recalculate.
Thanks
Rob H
- If i try to use a formula so show the equivalent of rev2CellX5 = rev1CellX5
I suspect when you are creating the formula, because it is between different workbooks, the formula is ending up with an absolute reference:
=[Rev2.xlsx]Sheet1!$X$5
This is default behaviour for creating links between workbooks. When copied down the $5 stays the same rather than extending down. Once you have created one formula eg for row 5 as above, amend the formula to remove the $ from in front of the row reference. This will then adjust as you copy down. If you are filling down while filtered, disable the filter before copying to the correct column as mentioned in previous comment.
Other option is that you have Manual Calculation set, press F9 to recalculate.
Thanks
Rob H
Thx Rob, I'll try that.
I'm using a temporary work around of resorting both tables so the records are all in their original order (that may be unnecessary but it's easier to double-check the results) and then copying and pasting the entire column from the rev1 workbook to the rev2 workbook.
My workaround works but it's not efficient.
Isn't there some sort of Paste Special feature where I can copy and paste from one workbook to another as I was trying to do without running into the problems i described? The formula idea was really just another workaround.
I'm using a temporary work around of resorting both tables so the records are all in their original order (that may be unnecessary but it's easier to double-check the results) and then copying and pasting the entire column from the rev1 workbook to the rev2 workbook.
My workaround works but it's not efficient.
Isn't there some sort of Paste Special feature where I can copy and paste from one workbook to another as I was trying to do without running into the problems i described? The formula idea was really just another workaround.
Active today
Unfortunately, the copy paste behaviour I described in my first comment is standard behaviour and I am not aware of a Paste Special option to avoid this; other than disabling the filter before copying and pasting with skip blanks option.
I take it I was correct with the assumption that there is also a filter in place???
Thanks
Rob H
I take it I was correct with the assumption that there is also a filter in place???
Thanks
Rob H
Rob, sorry, I somehow didn't see your first comment when I posted my reply above, so my questions was based only on having read your second comment.
Yes, both rev1 and rev 2 workbooks were filtered.
I hadn't used the MATCH function so I'll look into that.
The ideal setup (I'm thinking) would be some either built-in feature (which I'm unaware of) which would allow sync'ing between two workbooks where Excel does a cell by cell comparison and, depending on feature config options, gives the user the choice to keep the source value or the target value or something like that (obviously this gets messy if the structure of the workbooks are different even in the slightest bit).
Alternatively, a VBA script which could be set to compare the contents of a given range between workbooks, show the contents of workbook 1 cell beside workbook 2 cell whenever a difference was found, and give the user the choice to use one or the other, or use a default behaviour of say, update any changed info from 1 to 2 or something like that.
Unfortunately I'm a VBA newbie and not able to develop that quickly enough to be of use to myself on the project I'm working on.
Yes, both rev1 and rev 2 workbooks were filtered.
I hadn't used the MATCH function so I'll look into that.
The ideal setup (I'm thinking) would be some either built-in feature (which I'm unaware of) which would allow sync'ing between two workbooks where Excel does a cell by cell comparison and, depending on feature config options, gives the user the choice to keep the source value or the target value or something like that (obviously this gets messy if the structure of the workbooks are different even in the slightest bit).
Alternatively, a VBA script which could be set to compare the contents of a given range between workbooks, show the contents of workbook 1 cell beside workbook 2 cell whenever a difference was found, and give the user the choice to use one or the other, or use a default behaviour of say, update any changed info from 1 to 2 or something like that.
Unfortunately I'm a VBA newbie and not able to develop that quickly enough to be of use to myself on the project I'm working on.
Active today
Agree, straight cell to cell comparison relies on the workbooks being identical in structure and sorted in the same order; other issues occur if one workbook is open without the other and amendments are made, they end up out of kilter with each other.
However, using MATCH or LOOKUP or INDEX (to add another) functions doesn't rely on the rows being in the same order or same number of rows but they do need some unique identifier for each record in the table so that a match can be found for comparing records; ideally the columns should stay the same but not totally necessary so long as the headers stay the same.
With that in mind, assuming unique ID in column A of each worksheet and required value in column X (using your reference to X5 earlier), in an additonal column use the following:
=INDEX([mywb_rev1.xlsm]She
This look at the range Sheet1!A:X in Rev1 and return the value from Column X (column 24) based on matching the value in A5 of Rev2 with the same value in column A of Rev1. If you then just amend to:
=INDEX([mywb_rev1.xlsm]She
This will compare the Rev1 value of X5 to the Rev2 value of X5 and will return TRUE where they are the same or FALSE if they don't match. You can then extend your filter to include this column and show only the FALSE values as these are the values that have changed.
However, using MATCH or LOOKUP or INDEX (to add another) functions doesn't rely on the rows being in the same order or same number of rows but they do need some unique identifier for each record in the table so that a match can be found for comparing records; ideally the columns should stay the same but not totally necessary so long as the headers stay the same.
With that in mind, assuming unique ID in column A of each worksheet and required value in column X (using your reference to X5 earlier), in an additonal column use the following:
=INDEX([mywb_rev1.xlsm]She
This look at the range Sheet1!A:X in Rev1 and return the value from Column X (column 24) based on matching the value in A5 of Rev2 with the same value in column A of Rev1. If you then just amend to:
=INDEX([mywb_rev1.xlsm]She
This will compare the Rev1 value of X5 to the Rev2 value of X5 and will return TRUE where they are the same or FALSE if they don't match. You can then extend your filter to include this column and show only the FALSE values as these are the values that have changed.
Active today
Extending that a bit further:
Column Z =INDEX([mywb_rev1.xlsm]She
Column Z header Rev1
Column AA =X5
Column AA header Rev2
Gives the two values side by side
Column AB =IF(Z5=AA5,"","Check") - adds a comparison
Column AC - Add data Validation where user can input Rev1 or Rev2 for choosing which of the two values is correct.
Column AD =IF($AC2="",$Z2,INDEX($Z$1
This pulls the value from the Rev1 or Rev2 cells based on choice in AC.
If you apply filter to column AB to show only "Check", the user then only has to deal with those that are different. Once Rev1 or Rev2 has been chosen for each, disable the filter and copy and paste values from AD into X.
Basic logic flow:
Show both values
Use formula to compare
Make choice for correct value
Populate cell based on choice
Copy and paste values to overwrite
Hope that makes sense.
Thanks
Rob H
Column Z =INDEX([mywb_rev1.xlsm]She
Column Z header Rev1
Column AA =X5
Column AA header Rev2
Gives the two values side by side
Column AB =IF(Z5=AA5,"","Check") - adds a comparison
Column AC - Add data Validation where user can input Rev1 or Rev2 for choosing which of the two values is correct.
Column AD =IF($AC2="",$Z2,INDEX($Z$1
This pulls the value from the Rev1 or Rev2 cells based on choice in AC.
If you apply filter to column AB to show only "Check", the user then only has to deal with those that are different. Once Rev1 or Rev2 has been chosen for each, disable the filter and copy and paste values from AD into X.
Basic logic flow:
Show both values
Use formula to compare
Make choice for correct value
Populate cell based on choice
Copy and paste values to overwrite
Hope that makes sense.
Thanks
Rob H
Active today
See attached, maybe easier to see rather than text explanation above.
I have used two worksheets but the principle will be the same across two workbooks, if you move the Rev1 worksheet to another workbook, the formulas will change to accommodate the additional parts, ie filename.
Final action would be to copy and paste values from AD into column X, after disabling the filter.
Thanks
Rob H
Rev1-vs-Rev2.xlsx
I have used two worksheets but the principle will be the same across two workbooks, if you move the Rev1 worksheet to another workbook, the formulas will change to accommodate the additional parts, ie filename.
Final action would be to copy and paste values from AD into column X, after disabling the filter.
Thanks
Rob H
Rev1-vs-Rev2.xlsx
Featured Post
Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month11 days, 15 hours left to enroll
730 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.