Merging Files using Employe ID Number

[Pinkiredd]

I'm trying to reconcile billing that are in excel format. Both billings have
employee ID numbers. Is there a way I can merge the two files together and
get the desire results of who is not one what report?

 

[Joel]

I usually start with one report then add the second report to the first by
matching the ID's. If the ID's match add the second report to a new
column(s). If the second report ID does not match then add a new row to the
report and the data to the new column(s). The rows where there isn't datta
for both reports is the reults you are look for.

I need to know the number of columns ineach report and the column which
contains the ID's.

 

[JP]

This might help get you started.

http://www.excelguru.ca/blog/2008/10/08/conditional-format-to-compare-two-worksheets/

--JP

 

[Pinkiredd]

Both excel worksheets have 4 columns:
employe ID, last name, first name, cost

I'm want to see if excel can do the matching/reconciling instead of me doing
it manually.

 

[JP]

I just remembered another common way to do this: use a VLOOKUP formula
against the employee ID numbers in one report, if the numbers are
formatted the same way on both spreadsheets, you can immediately see
which number is on one report and not on the other.

Sorry but this is going to involve at least some effort on your part.

--JP

 

[Joel]

Try this code. It asumes the active sheet is where the first report is
located. The code ofpens a dialog to get the second book and assumes the
sheet name for this book is Sheet1. It then puts the cost from the 2nd book
into column E in the first book. If the ID is not found a new row is created
in Book 1.


Sub mergebooks()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox "Can't open file - Terminating Macro"
Exit Sub
End If

Set Bk1Sht = ThisWorkbook.ActiveSheet

Set bk2 = Workbooks.Open(Filename:=filetoopen)
Set bk2Sht = bk2.Sheets("Sheet1")


With Bk1Sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With bk2Sht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
ID = .Range("A" & RowCount)
LastName = .Range("B" & RowCount)
FirstName = .Range("C" & RowCount)
Cost = .Range("D" & RowCount)
With Bk1Sht
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlvalues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = LastName
.Range("C" & NewRow) = FirstName
.Range("E" & NewRow) = Cost
NewRow = NewRow + 1
Else
.Range("E" & c.Row) = Cost
End If
End With
RowCount = RowCount + 1
Loop
End With

bk2.Close savechanges:=False

End Sub

 

[Kathleen]

Hi,

I might have something similar to deal with. I've got a massive report with
columns up to EA. There are many columns with vlook up formulas, list boxes
and basic math. The new data from another file needs to be merged into the
Excel report. Along with eliminating the duplicates, is there a way to merge
the new information into the existing report and still maintain the column
formulas?
Kathleen