Merging two similar worksheets

[Ken]

Greetings, I have two worksheets which are similar and each have date and
time column data headings in addition to other columns.

For every row which has a matching date and time in both worksheets, I would
like to combine the data for that row from both worksheets into another
worksheet.

For every row with a date and time in either worksheet that does not have a
match in the other, that row will be discarded.

Sound familiar to anyone? Thanks.

 

[Joel]

What rules should we use in combining the two rows? Which columns have Date
and times? Which columns do the results go in?

 

[Ken]

Each sheet is in five columns with date and time at the end thusly:
2po228 placeholder 25.236200 9/2/2004 8:42:30 AM
2po228 placeholder 25.231090 9/2/2004 8:49:41 AM
2po228 placeholder 25.234030 9/2/2004 8:55:15 AM
Each sheet has different data in column 3. Placeholder is a blank reserved
column. The final sheet would look like the above excerpt except the
placeholder column will be populated with data from the other sheet whenever
date and time matched. The final sheet could be either a new sheet or a
modification of one of the originals, doesn't matter. Thanks

 

[Joel]

Try this. The macro will prompt for the compare file and then write to both
workbooks. The macro will leave both workbooks opend at the end and does not
save the files.

Sub CombineSheets()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Can't Open File, exiting Sub")
Exit Sub
End If

Set Comparebk = Workbooks.Open(Filename:=filetoopen)
Set CompareSht = Comparebk.Sheets("Sheet1")

With ThisWorkbook
Set NewSht1 = _
.Sheets.Add(after:=.Sheets(.Sheets.Count))
End With
With Comparebk
Set NewSht2 = _
.Sheets.Add(after:=.Sheets(.Sheets.Count))
End With

Set ThisSht = ThisWorkbook.Sheets("Sheet1")
With ThisSht
NewRowCount = 1
RowCount = 1
Do While .Range("A" & RowCount) <> ""
MyDate = .Range("D" & RowCount).Text
Mytime = .Range("E" & RowCount).Text
Data = .Range("C" & RowCount).Text

CompareRowCount = 1
With CompareSht
Do While .Range("A" & CompareRowCount) <> ""
CompareDate = .Range("D" & CompareRowCount).Text
CompareTime = .Range("E" & CompareRowCount).Text
CompareData = .Range("C" & RowCount).Text

If (MyDate = CompareDate) And _
(Mytime = CompareTime) Then

.Rows(CompareRowCount).Copy _
Destination:=NewSht2.Rows(NewRowCount)
NewSht2.Range("B" & NewRowCount) = Data
ThisSht.Rows(RowCount).Copy _
Destination:=NewSht1.Rows(NewRowCount)
NewSht1.Range("B" & NewRowCount) = CompareData
NewRowCount = NewRowCount + 1
End If

CompareRowCount = CompareRowCount + 1
Loop
End With

RowCount = RowCount + 1
Loop

End With

End Sub

 

[Ken]

Well it's close. With two small test files it worked OK with the caveat that
the correct results were found at the new sheet of the Comparebk file which
is opened by the macro but the results are shuffled wrong in the new sheet of
the ThisWorkbook file. When I tried it with the real files, one with 1376
rows and the other with 1076 rows, nothing was written and it gave an error
"Subscript out of range".

 

[Joel]

The subscript out of Range is probably due to the worksheet names not
matching. If I knew which line was highlighted when the error occured it
would help.

I check for data being shuffled and do not see any problems. What I did in
the code is when the rows matched I copied the matched row to the new sheet
in the same workbook where it was located. This put the the data in Column C
in the newsheet of the same workbook. I then took the data in Column B in
both workbooks and put it in the newsheet in the other Workbook in column B.

 

[Ken]

You are right. I am using Office 2007 and it renamed the sheets something
other than "Sheet1" when I brought data in. Now it works when I first
renamed the sheets to Sheet1. However, the shuffling still persists in the
column B data in the new sheet of the ThisWorkbook file. It seems to be off
in the row index.

 

[Joel]

The added rows in both worksheets are the same. That may account for the
shuffling. When I found two sheetnames the same I simply added them both at
the same time. I didn't think it mattered what order the rows where added
into the new worksheet.

 

[Ken]

The order does matter. I was thinking this might be a common task. It is
similar to the following scenario. Say you have a sales team and each
salesman submits a worksheet with dates and amount sold. Now if you want to
see a column for each saleman with results for any given day in the same
place this is something you could use this macro for. Thanks.

 

[Joel]

I finally found the problem after looking five times

from
CompareData = .Range("C" & RowCount).Text
to
CompareData = .Range("C" & CompareRowCount).Text

 

[Ken]

Yes, you are right, that fixed it. Thanks

I finally found the problem after looking five times

from
CompareData = .Range("C" & RowCount).Text
to
CompareData = .Range("C" & CompareRowCount).Text