Solved

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

Posted on 2015-02-20
9
183 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
  • 6
  • 3
9 Comments
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:qeng
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 500 total points
Comment Utility
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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
Fantastic help.  Great stuff Rob, thanks for staying with me on this one.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now