Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
What rules should we use in combining the two rows? Which columns have Date
and times? Which columns do the results go in? "Ken" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
I finally found the problem after looking five times
from CompareData = .Range("C" & RowCount).Text to CompareData = .Range("C" & CompareRowCount).Text "Ken" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two similar worksheets
Yes, you are right, that fixed it. Thanks
"Joel" wrote: I finally found the problem after looking five times from CompareData = .Range("C" & RowCount).Text to CompareData = .Range("C" & CompareRowCount).Text "Ken" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: What rules should we use in combining the two rows? Which columns have Date and times? Which columns do the results go in? "Ken" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Similar Data in worksheets | Excel Discussion (Misc queries) | |||
Merging slightly similar records | Excel Discussion (Misc queries) | |||
Two Worksheets similar data | Excel Discussion (Misc queries) | |||
Combining two similar worksheets and showing changes | New Users to Excel | |||
Comparing data in two similar worksheets | Excel Discussion (Misc queries) |