Hi,
I am trying to merge the data of two files into one file.
say I am to read from file A and file B and store it in file C.
A and B have department numbers, project number and entity and some other
data.
A and B will have the same project, dept and entity numbers. but ether of
them may be missing some data.
so for that i need to get them into a 3rd file.
the third file should have the project dept and entity numbers from both the
files and their corresponding data but they should not be douplicated in file
C.
can any one help me with this.

thanks

I'm not certain I've interpreted your needs completely, but I think the code
below will give us a starting point.

This code will test to make sure you have at least 3 (but not more than 4)
workbooks open. Presumably you have file A and file B along with file C
open, and you may or may not have PERSONAL.XLS open. It will then
arbitrarily pick either file A or B as the primary book and just copy all
entries in it into file C. Then it compares the Dept & Project entries it
has copied to those in the remaining workbook. If there is an entry in the
remaining book (A or B) that has both Dept and Project entry that doesn't
match anything in file C, it copies the entry into file C as a new entry.

It does nothing if there is a match of both the Dept and Project info in
book C and the remaining book.

To use the code, open or create the file C .xls workbook. Use [Alt]+[F11]
to open the VB Editor. In the VBE, choose Insert | Module and then copy and
paste the code below into it, making modifications of the various CONST
values as required for your setup. To run it, open all 3 workbooks (A, B and
C) and in file C choose Tools | Macro | Macros and select the "MergeData"
macro and click the [Run] button.

Here's the code, watch for any extra breaks in code lines made by the system
here. I've tried to keep the entries short enough so that it won't happen,
but sometimes the system fools me.

Sub MergeData()
'Change the value of the various "Const"
'declarations to reflect your real-world
'setup.
'
'dataSheetName is the name of the
'sheet in the other two books with data
'to be collated. Assumed to be the same
'in both other workbooks
Const dataSheetName = "Our Data"
'these define the columns that Department
'and Project IDs are in
Const deptIDCol = "A"
Const projIDCol = "B"
'combinedDataSheetName is the name of
'the sheet in this workbook that will
'contain the collated data
Const combinedDataSheetName = "BookC_Combined"
'end of user redefinable Const values

Dim BookAName As String
Dim BookBName As String
Dim BookA As Workbook
Dim BookASheet As Worksheet
Dim BookB As Workbook
Dim BookBSheet As Worksheet
Dim BookCSheet As Worksheet
Dim combinedSheet As Worksheet ' in this workbook
Dim sourceRange As Range ' dept list in BookB
Dim anySourceEntry As Range ' single cell
Dim testRange As Range ' dept list in this workbook
Dim anyTestEntry As Range ' single cell
Dim copyRange As Range
Dim aCount As Integer
Dim colOffset As Long ' offset from Dept col to Proj col
Dim tempRowNum As Long
Dim matchFlag As Boolean

'test to find if we have
'3 workbooks open
aCount = Application.Workbooks.Count
If aCount = 4 Then
'may be OK if one of them is PERSONAL.XLS
aCount = 0 ' reset it
For Each BookA In Application.Workbooks
If UCase(BookA.Name) < "PERSONAL.XLS" Then
aCount = aCount + 1
End If
Next
ElseIf aCount 4 Then
MsgBox "You have too many workbooks open."
Exit Sub
End If
'at this point aCount should be 3 to continue
If aCount < 3 Then
MsgBox "You don't have the proper 3 workbooks open."
Exit Sub
End If
'assign variables to the other two source workbooks
For Each BookA In Application.Workbooks
If BookA.Name < ThisWorkbook.Name And _
UCase(BookA.Name) < "PERSONAL.XLS" Then
If BookAName = "" Then
BookAName = BookA.Name
Else
BookBName = BookA.Name
End If
End If
Next
Set BookA = Workbooks(BookAName)
Set BookASheet = BookA.Worksheets(dataSheetName)
Set BookB = Workbooks(BookBName)
Set BookBSheet = BookB.Worksheets(dataSheetName)
Set combinedSheet = _
ThisWorkbook.Worksheets(combinedDataSheetName)
'start by simply copying everything from the
'data sheet in BookA into this workbook.
Set copyRange = BookASheet.UsedRange

'make sure this workbook and the data sheet
'are the active workbook and sheet
ThisWorkbook.Activate
combinedSheet.Select
Application.ScreenUpdating = False ' speed things up
'clear old data from this workbook
combinedSheet.Cells.Clear
combinedSheet.Range("A1").Select
copyRange.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
'we no longer even need BookA!
'release resources back to the system
Set copyRange = Nothing
Set BookASheet = Nothing
Set BookA = Nothing
'calculate offset from Dept column to Proj column
colOffset = Range(projIDCol & 1).Column - _
Range(deptIDCol & 1).Column
'set up to examine Dept IDs in this workbook
Set testRange = combinedSheet.Range(deptIDCol & "1:" & _
combinedSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
Set sourceRange = BookBSheet.Range(deptIDCol & "1:" & _
BookBSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
'begin comparing entries
For Each anySourceEntry In sourceRange ' in BookB
matchFlag = False ' reset
For Each anyTestEntry In testRange ' in this workbook
If anySourceEntry = anyTestEntry Then
If Trim(anySourceEntry.Offset(0, colOffset)) = _
Trim(anyTestEntry.Offset(0, colOffset)) Then
'we have a match on Dept and Project
matchFlag = True
Exit For ' get out of the inner loop now
End If
End If
Next
'is this a new entry from BookB?
If Not matchFlag Then
'yes, new entry, just add to the bottom
'of the worksheet.
anySourceEntry.EntireRow.Copy
tempRowNum = combinedSheet.Range(deptIDCol & _
Rows.Count).End(xlUp).Offset(1, 0).Row
combinedSheet.Rows(tempRowNum & ":" & tempRowNum). _
PasteSpecial Paste:=xlPasteAll
End If
Next
'cleanup and release resources
combinedSheet.Range("A1").Select
Set testRange = Nothing
Set combinedSheet = Nothing
Set BookBSheet = Nothing
Set BookB = Nothing
End Sub

"Jeegar" wrote:

Hi,
I am trying to merge the data of two files into one file.
say I am to read from file A and file B and store it in file C.
A and B have department numbers, project number and entity and some other
data.
A and B will have the same project, dept and entity numbers. but ether of
them may be missing some data.
so for that i need to get them into a 3rd file.
the third file should have the project dept and entity numbers from both the
files and their corresponding data but they should not be douplicated in file
C.
can any one help me with this.

thanks

Thanks JLatham,
i think this should do the work.
but i'll be able to check that only on tuesday when i go to work
thanks a lot for your time and efford.
Jeegar


"JLatham" wrote:

I'm not certain I've interpreted your needs completely, but I think the code
below will give us a starting point.

This code will test to make sure you have at least 3 (but not more than 4)
workbooks open. Presumably you have file A and file B along with file C
open, and you may or may not have PERSONAL.XLS open. It will then
arbitrarily pick either file A or B as the primary book and just copy all
entries in it into file C. Then it compares the Dept & Project entries it
has copied to those in the remaining workbook. If there is an entry in the
remaining book (A or B) that has both Dept and Project entry that doesn't
match anything in file C, it copies the entry into file C as a new entry.

It does nothing if there is a match of both the Dept and Project info in
book C and the remaining book.

To use the code, open or create the file C .xls workbook. Use [Alt]+[F11]
to open the VB Editor. In the VBE, choose Insert | Module and then copy and
paste the code below into it, making modifications of the various CONST
values as required for your setup. To run it, open all 3 workbooks (A, B and
C) and in file C choose Tools | Macro | Macros and select the "MergeData"
macro and click the [Run] button.

Here's the code, watch for any extra breaks in code lines made by the system
here. I've tried to keep the entries short enough so that it won't happen,
but sometimes the system fools me.

Sub MergeData()
'Change the value of the various "Const"
'declarations to reflect your real-world
'setup.
'
'dataSheetName is the name of the
'sheet in the other two books with data
'to be collated. Assumed to be the same
'in both other workbooks
Const dataSheetName = "Our Data"
'these define the columns that Department
'and Project IDs are in
Const deptIDCol = "A"
Const projIDCol = "B"
'combinedDataSheetName is the name of
'the sheet in this workbook that will
'contain the collated data
Const combinedDataSheetName = "BookC_Combined"
'end of user redefinable Const values

Dim BookAName As String
Dim BookBName As String
Dim BookA As Workbook
Dim BookASheet As Worksheet
Dim BookB As Workbook
Dim BookBSheet As Worksheet
Dim BookCSheet As Worksheet
Dim combinedSheet As Worksheet ' in this workbook
Dim sourceRange As Range ' dept list in BookB
Dim anySourceEntry As Range ' single cell
Dim testRange As Range ' dept list in this workbook
Dim anyTestEntry As Range ' single cell
Dim copyRange As Range
Dim aCount As Integer
Dim colOffset As Long ' offset from Dept col to Proj col
Dim tempRowNum As Long
Dim matchFlag As Boolean

'test to find if we have
'3 workbooks open
aCount = Application.Workbooks.Count
If aCount = 4 Then
'may be OK if one of them is PERSONAL.XLS
aCount = 0 ' reset it
For Each BookA In Application.Workbooks
If UCase(BookA.Name) < "PERSONAL.XLS" Then
aCount = aCount + 1
End If
Next
ElseIf aCount 4 Then
MsgBox "You have too many workbooks open."
Exit Sub
End If
'at this point aCount should be 3 to continue
If aCount < 3 Then
MsgBox "You don't have the proper 3 workbooks open."
Exit Sub
End If
'assign variables to the other two source workbooks
For Each BookA In Application.Workbooks
If BookA.Name < ThisWorkbook.Name And _
UCase(BookA.Name) < "PERSONAL.XLS" Then
If BookAName = "" Then
BookAName = BookA.Name
Else
BookBName = BookA.Name
End If
End If
Next
Set BookA = Workbooks(BookAName)
Set BookASheet = BookA.Worksheets(dataSheetName)
Set BookB = Workbooks(BookBName)
Set BookBSheet = BookB.Worksheets(dataSheetName)
Set combinedSheet = _
ThisWorkbook.Worksheets(combinedDataSheetName)
'start by simply copying everything from the
'data sheet in BookA into this workbook.
Set copyRange = BookASheet.UsedRange

'make sure this workbook and the data sheet
'are the active workbook and sheet
ThisWorkbook.Activate
combinedSheet.Select
Application.ScreenUpdating = False ' speed things up
'clear old data from this workbook
combinedSheet.Cells.Clear
combinedSheet.Range("A1").Select
copyRange.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
'we no longer even need BookA!
'release resources back to the system
Set copyRange = Nothing
Set BookASheet = Nothing
Set BookA = Nothing
'calculate offset from Dept column to Proj column
colOffset = Range(projIDCol & 1).Column - _
Range(deptIDCol & 1).Column
'set up to examine Dept IDs in this workbook
Set testRange = combinedSheet.Range(deptIDCol & "1:" & _
combinedSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
Set sourceRange = BookBSheet.Range(deptIDCol & "1:" & _
BookBSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
'begin comparing entries
For Each anySourceEntry In sourceRange ' in BookB
matchFlag = False ' reset
For Each anyTestEntry In testRange ' in this workbook
If anySourceEntry = anyTestEntry Then
If Trim(anySourceEntry.Offset(0, colOffset)) = _
Trim(anyTestEntry.Offset(0, colOffset)) Then
'we have a match on Dept and Project
matchFlag = True
Exit For ' get out of the inner loop now
End If
End If
Next
'is this a new entry from BookB?
If Not matchFlag Then
'yes, new entry, just add to the bottom
'of the worksheet.
anySourceEntry.EntireRow.Copy
tempRowNum = combinedSheet.Range(deptIDCol & _
Rows.Count).End(xlUp).Offset(1, 0).Row
combinedSheet.Rows(tempRowNum & ":" & tempRowNum). _
PasteSpecial Paste:=xlPasteAll
End If
Next
'cleanup and release resources
combinedSheet.Range("A1").Select
Set testRange = Nothing
Set combinedSheet = Nothing
Set BookBSheet = Nothing
Set BookB = Nothing
End Sub

"Jeegar" wrote:

Hi,
I am trying to merge the data of two files into one file.
say I am to read from file A and file B and store it in file C.
A and B have department numbers, project number and entity and some other
data.
A and B will have the same project, dept and entity numbers. but ether of
them may be missing some data.
so for that i need to get them into a 3rd file.
the third file should have the project dept and entity numbers from both the
files and their corresponding data but they should not be douplicated in file
C.
can any one help me with this.

thanks

Let me know how it goes.

The only "catch" to it that I know of is that the labels in row 1 of both
bookA and bookB for the department and project columns need to be the same,
otherwise the label row will get copied numerous times into bookC. That's
because I do the comparison to include row 1, we could change that to start
at a higher row number easy enough.

"Jeegar" wrote:

Thanks JLatham,
i think this should do the work.
but i'll be able to check that only on tuesday when i go to work
thanks a lot for your time and efford.
Jeegar


"JLatham" wrote:

I'm not certain I've interpreted your needs completely, but I think the code
below will give us a starting point.

This code will test to make sure you have at least 3 (but not more than 4)
workbooks open. Presumably you have file A and file B along with file C
open, and you may or may not have PERSONAL.XLS open. It will then
arbitrarily pick either file A or B as the primary book and just copy all
entries in it into file C. Then it compares the Dept & Project entries it
has copied to those in the remaining workbook. If there is an entry in the
remaining book (A or B) that has both Dept and Project entry that doesn't
match anything in file C, it copies the entry into file C as a new entry.

It does nothing if there is a match of both the Dept and Project info in
book C and the remaining book.

To use the code, open or create the file C .xls workbook. Use [Alt]+[F11]
to open the VB Editor. In the VBE, choose Insert | Module and then copy and
paste the code below into it, making modifications of the various CONST
values as required for your setup. To run it, open all 3 workbooks (A, B and
C) and in file C choose Tools | Macro | Macros and select the "MergeData"
macro and click the [Run] button.

Here's the code, watch for any extra breaks in code lines made by the system
here. I've tried to keep the entries short enough so that it won't happen,
but sometimes the system fools me.

Sub MergeData()
'Change the value of the various "Const"
'declarations to reflect your real-world
'setup.
'
'dataSheetName is the name of the
'sheet in the other two books with data
'to be collated. Assumed to be the same
'in both other workbooks
Const dataSheetName = "Our Data"
'these define the columns that Department
'and Project IDs are in
Const deptIDCol = "A"
Const projIDCol = "B"
'combinedDataSheetName is the name of
'the sheet in this workbook that will
'contain the collated data
Const combinedDataSheetName = "BookC_Combined"
'end of user redefinable Const values

Dim BookAName As String
Dim BookBName As String
Dim BookA As Workbook
Dim BookASheet As Worksheet
Dim BookB As Workbook
Dim BookBSheet As Worksheet
Dim BookCSheet As Worksheet
Dim combinedSheet As Worksheet ' in this workbook
Dim sourceRange As Range ' dept list in BookB
Dim anySourceEntry As Range ' single cell
Dim testRange As Range ' dept list in this workbook
Dim anyTestEntry As Range ' single cell
Dim copyRange As Range
Dim aCount As Integer
Dim colOffset As Long ' offset from Dept col to Proj col
Dim tempRowNum As Long
Dim matchFlag As Boolean

'test to find if we have
'3 workbooks open
aCount = Application.Workbooks.Count
If aCount = 4 Then
'may be OK if one of them is PERSONAL.XLS
aCount = 0 ' reset it
For Each BookA In Application.Workbooks
If UCase(BookA.Name) < "PERSONAL.XLS" Then
aCount = aCount + 1
End If
Next
ElseIf aCount 4 Then
MsgBox "You have too many workbooks open."
Exit Sub
End If
'at this point aCount should be 3 to continue
If aCount < 3 Then
MsgBox "You don't have the proper 3 workbooks open."
Exit Sub
End If
'assign variables to the other two source workbooks
For Each BookA In Application.Workbooks
If BookA.Name < ThisWorkbook.Name And _
UCase(BookA.Name) < "PERSONAL.XLS" Then
If BookAName = "" Then
BookAName = BookA.Name
Else
BookBName = BookA.Name
End If
End If
Next
Set BookA = Workbooks(BookAName)
Set BookASheet = BookA.Worksheets(dataSheetName)
Set BookB = Workbooks(BookBName)
Set BookBSheet = BookB.Worksheets(dataSheetName)
Set combinedSheet = _
ThisWorkbook.Worksheets(combinedDataSheetName)
'start by simply copying everything from the
'data sheet in BookA into this workbook.
Set copyRange = BookASheet.UsedRange

'make sure this workbook and the data sheet
'are the active workbook and sheet
ThisWorkbook.Activate
combinedSheet.Select
Application.ScreenUpdating = False ' speed things up
'clear old data from this workbook
combinedSheet.Cells.Clear
combinedSheet.Range("A1").Select
copyRange.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
'we no longer even need BookA!
'release resources back to the system
Set copyRange = Nothing
Set BookASheet = Nothing
Set BookA = Nothing
'calculate offset from Dept column to Proj column
colOffset = Range(projIDCol & 1).Column - _
Range(deptIDCol & 1).Column
'set up to examine Dept IDs in this workbook
Set testRange = combinedSheet.Range(deptIDCol & "1:" & _
combinedSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
Set sourceRange = BookBSheet.Range(deptIDCol & "1:" & _
BookBSheet.Range(deptIDCol & Rows.Count).End(xlUp).Address)
'begin comparing entries
For Each anySourceEntry In sourceRange ' in BookB
matchFlag = False ' reset
For Each anyTestEntry In testRange ' in this workbook
If anySourceEntry = anyTestEntry Then
If Trim(anySourceEntry.Offset(0, colOffset)) = _
Trim(anyTestEntry.Offset(0, colOffset)) Then
'we have a match on Dept and Project
matchFlag = True
Exit For ' get out of the inner loop now
End If
End If
Next
'is this a new entry from BookB?
If Not matchFlag Then
'yes, new entry, just add to the bottom
'of the worksheet.
anySourceEntry.EntireRow.Copy
tempRowNum = combinedSheet.Range(deptIDCol & _
Rows.Count).End(xlUp).Offset(1, 0).Row
combinedSheet.Rows(tempRowNum & ":" & tempRowNum). _
PasteSpecial Paste:=xlPasteAll
End If
Next
'cleanup and release resources
combinedSheet.Range("A1").Select
Set testRange = Nothing
Set combinedSheet = Nothing
Set BookBSheet = Nothing
Set BookB = Nothing
End Sub

"Jeegar" wrote:

Hi,
I am trying to merge the data of two files into one file.
say I am to read from file A and file B and store it in file C.
A and B have department numbers, project number and entity and some other
data.
A and B will have the same project, dept and entity numbers. but ether of
them may be missing some data.
so for that i need to get them into a 3rd file.
the third file should have the project dept and entity numbers from both the
files and their corresponding data but they should not be douplicated in file
C.
can any one help me with this.

thanks