Solved

How to Copy Data from Sorted Excel 2010 Table to Same Sorted Table in another Workbook

Posted on 2015-02-20
9
203 Views
Last Modified: 2016-02-11
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)?
0
Comment
Question by:qeng
[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
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40620904
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
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40620911
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
0
 

Author Comment

by:qeng
ID: 40620923
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40620927
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
0
 

Author Comment

by:qeng
ID: 40620952
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.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40621020
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]Sheet1!$A:$X,MATCH($A5,[mywb_rev1.xlsm]Sheet1!$A:$A,0),24)

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]Sheet1!$A:$X,MATCH($A5,[mywb_rev1.xlsm]Sheet1!$A:$A,0),24)=X5

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.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
ID: 40621051
Extending that a bit further:

Column Z  =INDEX([mywb_rev1.xlsm]Sheet1!$A:$X,MATCH($A5,[mywb_rev1.xlsm]Sheet1!$A:$A,0),24)
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:$AA$1000,ROW(),MATCH($AC2,$Z$1:$AA$1,0)))

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
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40621080
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
0
 

Author Closing Comment

by:qeng
ID: 40623385
Fantastic help.  Great stuff Rob, thanks for staying with me on this one.
0

Featured Post

Office 365 Training for IT Pros

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.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question