- Excel Compare Tool
- Customize Quick Access For Excel Compare And Merge Workbooks Free
- Customize Quick Access For Excel Compare And Merge Workbooks Greyed Out
Excel 2010: Merging Copies of a Shared Workbook. Excel Details: To compare and merge workbooks: Open a copy of the shared workbook. Click the Compare and Merge Workbooks command on the Quick Access toolbar. If prompted, allow Excel to save your workbook. › Verified 7 days ago. On the Quick Access Toolbar, click Compare and Merge Workbooks. If prompted, save the workbook. In the Select Files to Merge into Current Workbook dialog box, click the copy of the workbook that contains the changes you want to merge. To merge multiple copies at the same time, hold down Ctrl or Shift and click the file names, then click OK. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them. Contents hide Method 1: Copy the cell ranges. Method 2: Manually copy worksheets. Method 3: Use the INDIRECT formula. Method 4: Merge files with a simple VBA macro.
I'm hoping this can be done with a macro...
I have a workbook with two worksheets, several hundred rows each. The columns in each worksheet are identical, with A:A being a unique ID field. The data is sorted prior to output so that the records for each ID appear in the same row in each worksheet. My problem is that Sheet2 contains an incomplete recordset that needs to be compared to Sheet1 which is complete.
I want to compare the data in Sheet2,A:A to the data in Sheet1,A:A and perform a row insert based on the result.
For example, if Sheet2,A2 = Sheet1,A2 then nothing happens, but if the values are different, I want to insert a new, blank row automatically in Sheet2, above A2... then loop through the rest of the records and repeat as necessary.
Please tell me this is possible with a macro. What once was a small monthly task is now a daily request that can no longer be accomplished manually...
Val
Val,
Since you use terms such as 'unique ID' and 'recordset', I'm a little curious as to why you aren't using Access. I've made this comment before, but it applies again, the task that you want a macro for is trivial when performed in a database application such as Access. Of course, if you don't have Access, then it's understandable why you're not using it. But if you do have Access it would really be in your best interest to learn it. This little task is easy in Access. [end preaching ;-))]
However, I'll work up a little macro to do this operation in Excel. Give me a little time (like about 15 - 30 minutes) and I'll post it.
Maybe someone will beat me to it. LOL
in a bit
The macro
Val,
Here it is (at least I hope this is what you wanted :-)) ):
Sub InsertRecord()
Dim Sht1LastRow As Long
Dim i As Long
Dim j As Long
Sht1LastRow = Sheets('Sheet1').Cells(65536, 1).End(xlUp).Row
j = 2
For i = 2 To Sht1LastRow
If Sheets('Sheet2').Cells(j, 1).Value <> Sheets('Sheet1').Cells(i, 1).Value Then
Sheets('Sheet2').Rows(j).Insert Shift:=xlDown
' Sheets('Sheet2').Cells(j, 1).Value = Sheets('Sheet1').Cells(i, 1).Value
End If
j = j + 1
Next i
End Sub
You said that you wanted a blank row inserted. This does that. If, however, you want the unique ID added after the row is inserted, then 'uncomment' (i.e., remove the single quote (') from the line which reads ' Sheets('Sheet2').Cells(j, 1).Value = Sheets('Sheet1').Cells(i, 1).Value '.
Adjust the sheet names if/as necessary.
enjoy
,
: I'm hoping this can be done with a macro... : I have a workbook with two worksheets, several hundred rows each. The columns in each worksheet are identical, with A:A being a unique ID field. The data is sorted prior to output so that the records for each ID appear in the same row in each worksheet. My problem is that Sheet2 contains an incomplete recordset that needs to be compared to Sheet1 which is complete. : I want to compare the data in Sheet2,A:A to the data in Sheet1,A:A and perform a row insert based on the result. : For example, if Sheet2,A2 = Sheet1,A2 then nothing happens, but if the values are different, I want to insert a new, blank row automatically in Sheet2, above A2... then loop through the rest of the records and repeat as necessary. : Please tell me this is possible with a macro. What once was a small monthly task is now a daily request that can no longer be accomplished manually... : Val
Re: The macro
You're right, Access is the culprit behind this. A little background...
The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2).
All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2.
Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets.
I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-)
Thanks ! ~Val , Here it is (at least I hope this is what you wanted :-)) ): Sub InsertRecord() Dim Sht1LastRow As Long Dim i As Long Dim j As Long Sht1LastRow = Sheets('Sheet1').Cells(65536, 1).End(xlUp).Row j = 2 For i = 2 To Sht1LastRow If Sheets('Sheet2').Cells(j, 1).Value <> Sheets('Sheet1').Cells(i, 1).Value Then Sheets('Sheet2').Rows(j).Insert Shift:=xlDown ' Sheets('Sheet2').Cells(j, 1).Value = Sheets('Sheet1').Cells(i, 1).Value End If j = j + 1 Next i End Sub You said that you wanted a blank row inserted. This does that. If, however, you want the unique ID added after the row is inserted, then 'uncomment' (i.e., remove the single quote (') from the line which reads ' Sheets('Sheet2').Cells(j, 1).Value = Sheets('Sheet1').Cells(i, 1).Value '. Adjust the sheet names if/as necessary. enjoy
Excel Compare Tool
:
Customize Quick Access For Excel Compare And Merge Workbooks Free
It worked! Thanks a bunch, Bariloche!
you've literally trimmed off hours of work for me. I'm off to go enjoy what's left of my weekend.
Cheers!
Val
Re: The macro
Val,
Customize Quick Access For Excel Compare And Merge Workbooks Greyed Out
What are you going to do with 'Sheet2' after you run the macro. Do you want to update one of your Access tables or create a report or what? Looks like you're looking for folks who are enrolled but haven't completed the training?
Give me a little more info and maybe I can post some SQL so that you can get Access to do what you need.
thanks
You're right, Access is the culprit behind this. A little background... The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2). All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2. Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets. I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-) Thanks ! ~Val
Re: It worked! Thanks a bunch, Bariloche!
Val,
You're welcome. Post later this weekend, if you'd like, and I'll see if I can't help you with the Access side of things. I'm pretty sure that we can make this chore a thing of the past.
take care
you've literally trimmed off hours of work for me. I'm off to go enjoy what's left of my weekend. Cheers!
Re: The macro
Sheet2 is being used create a summary report on who has not yet completed training. This report will be used to communicate the incomplete status to other staff.
The reason for inserting a blank row is so the operators can see at a quick glance (the blank rows are later highlighted using conditional formatting) which clients are incomplete, and when contacting the clients by phone, be able to write notes that will later be entered into the database.
The macro works great! Thanks again for your help!
~Val , What are you going to do with 'Sheet2' after you run the macro. Do you want to update one of your Access tables or create a report or what? Looks like you're looking for folks who are enrolled but haven't completed the training? Give me a little more info and maybe I can post some SQL so that you can get Access to do what you need. thanks
: You're right, Access is the culprit behind this. A little background... : The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2). : All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2. : Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets. : I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-) : Thanks ! ~Val