Merging two sheets

[Peskarik]

Hello everyone,

I have the following problem, that I don't know how to solve with Excel VBA (but which is easily solvable in R with the merge function, if that may help you understand my problem).

THE SETUP:

I have a workbook that contains a number of sheets.

On each sheet there are four columns, the first row of each column is the name. The column placements and column names are similar across the sheets.

The first column (Comp_Name) contains company names.
The second column (Date) contains the dates. The dates are in format like 21.12.2007
The third column (Return) contains returns for each company on that specific date.

PROBLEM:

Some companies (together with same dates and returns) appear on several sheets.
I am copying all the data from each of the sheets into one worksheet in another workbook. The data I have copied is used in formulas in that workbook. Having redundancies is not an option.

WHAT I WANT TO DO:

I want to merge the sheets by name and date into one big array (on a separate sheet), so that I do not have repeating companies, but still have informational efficiency. In R it is very simple to do, one merges two arrays, then one merges the newly merged array with another array, and so on.
Is it possible to merge arrays in Excel?

Hopefully someone had this kind of problem and can provide me with the clues, or even a piece of code.

Thanks in advance!

[mdmackillop]

Can you post a sample workbook?

[Peskarik]

Can you post a sample workbook?

How do I do that?
Just attach in the reply to the thread?

[mdmackillop]

Use Manage Attachments in the Go Advanced reply section

[Peskarik]

Hi again.
Sorry, have been busy with other things.
I have uploaded a small Excel workbook, which contains 3 sheets. Last sheet is empty. First two sheets contain data on some (imaginary) companies. Companies Copula and EngleNg appear on both sheets, and Copula has two more returns on the second sheet (returns for November and December 2006). EngleNg has same entries on both sheets. On the second sheet, the last company SubzeroHK does not have returns for the last two months.

Problem:
To merge sheet 1 and 2 into one array without repeating companies but so that Copula has all the data from both sheets. The final array is to be placed on the empty sheet 3. So, sheet 3 should contain four columns and all non-repeating data from sheets 1 and 2, sorted by company name.

Please, if knows how to do this with VBA that would be of immence help to me! I browsed the web but did not find anything helpful regarding this problem of mine.

Thank you in advance for your help.

[mdmackillop]

[vba]Sub Macro1()

Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet
Dim shTmp As Worksheet


Application.ScreenUpdating = False
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Set Sh3 = Sheets("Sheet3")
Set shTmp = Sheets.Add

With Sh1
Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 4).Copy _
shTmp.Range("A1")
End With

With Sh2
Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).Resize(, 4).Copy _
shTmp.Range("A1").End(xlDown).Offset(1)
End With

With Range(shTmp.Cells(1, 1), shTmp.Cells(1, 1).End(xlDown)).Resize(, 4)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Sh3.Range("A1")
End With

With Sh3
.Columns("A").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.DisplayAlerts = False
shTmp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

[/vba]

[Peskarik]

THank you for this!

It almost works.
When I run this part on the real data:
[VBA]
With Range(shTmp.Cells(1, 1), shTmp.Cells(1, 1).End(xlDown)).Resize(, 4)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Copy Sh3.Range("A1")
End With
[/VBA]

It copies the filtered fields to Sh3 but throws an error message:
"Run-time error '1004'
Copy method of Range class failed"

and then it does not run further (no sorting).

I thought maybe it was some kind of pasting special cells problem and played around with immediate window, running line by line, but no.
What could be the problem? I have Excel 2003 Sp2.
Unfortunately, I cannot send the original data, because it is proprietory.

But in my data I have two other irregularities:
1) One company appears on Sh1 and Sh2, but on Sh2 for one of the dates it does not have return figure, while on Sh1 it has return figure on that date. When I copy into ShTmp the row where this company has missing return is pasted at the bottom, while the row where this company has return is pasted further up. There is therefore duplication again. How do I deal effectively with that?

2)Another company for some reason has duplicating dates, so that row 1 and row 2 are similar, then row 3 and row 4 are similar, and so on. The time series for that company is then duplicated. But duplication is on the same sheet Sh1 and the company does not appear on the Sh2, so into ShTmp it is copied as-is, that is, with duplicated rows.

Can this be solved efficiently with another application of some filter function?

I hope I do not take much of your time, mdmackillop, with my questions.
Could you please help me out a little further?
If you need a new file, I will make it this evening and upload it here.

Thanks in advance!

[mdmackillop]

Can you repost your sample to demonstrate the problem items, highlighting them and showing what you expect to see on Sheet 3 for these items.

[Peskarik]

I will repost tonight and explain everything.
Thanks in advance for help!

[Peskarik]

SOrry for the delay.

Here is the new Excel file that contains:
1) Two repeated time series on sheet 1 and 2 (these are called Copula LTD. and EngleNg Corp.)
2) Company named Acme Electronics AG on the first sheet has duplicated entries.
3) EngleNg Corp. has a return figure for 01.12.2006 on Sheet 1, but not on Sheet 2.

What I want:
To merge the two sheets on Sheet 3, in such a way that there are no repeating companies, the duplicated entries (Acme case) are not included, and all information is fully used (that is, if there is missing return on one sheet, but there is a duplicate on another sheet that contains the return, like in case 3 above, then the return should be posted on Sheet 3).

The code in the previous post does not handle cases 2) and 3), unfortunately.
I am thankful to mdmackillop for the code, I learn new things from it!
Hopefully, I get some help with 2) and 3) as well.
Thank you in advance for your help!

[mdmackillop]

I don't see the problem. Will you please run my earlier code and highlight to problem results on sheet 3. In blank returns are not valid, then just search for and delete rows with blank values in this field.

[mikerickson]

Try changing the offending line to

[VBA]With Range(shTmp.Cells(1, 1), shTmp.Cells(1, 1).End(xlDown)).Resize(, 4)
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh3.Range("A1"), Unique:=True
End With
[/VBA]

[Peskarik]

I don't see the problem. Will you please run my earlier code and highlight to problem results on sheet 3. In blank returns are not valid, then just search for and delete rows with blank values in this field.

Hello.
Yes, I ran your previous code on my last Excel example workbook and it worked fine without any error messages. The duplicated dates for ACME were handled properly. The only problem remaining is the duplicated entry for EngleNg for the last month (no return one and one with the return). What would be the most efficient piece of code to handle that duplication? (Of course, I can write my own code but I am pretty sure it would not be as efficient due to my lack of experience).

I ran this on my Excel 2007 SP1.
The workbook that I cannot attach here is on a PC with Excel 2003.
Running the code on the original workbook on Excel 2003, the duplicates (similar to ACME as well similar to EngleNg) are not handled, and the error message pops up. WEird.

[Peskarik]

Oh, here is btw the example workbook that I posted earlier and on which I ran the code. The results are on Sheet 3. The EngleNg problem is marked red on rows 73-74 on Sheet 3.

[Peskarik]

Try changing the offending line to

[vba]With Range(shTmp.Cells(1, 1), shTmp.Cells(1, 1).End(xlDown)).Resize(, 4)
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh3.Range("A1"), Unique:=True
End With
[/vba]

Thanks a lot!
I will try this code on the Excel 2003 file tomorrow.