Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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? -- Ms. Davis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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. "Pinkiredd" wrote: 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? -- Ms. Davis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
This might help get you started.
http://www.excelguru.ca/blog/2008/10...wo-worksheets/ --JP On Oct 14, 1:22*pm, Pinkiredd wrote: 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? -- Ms. Davis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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. -- Ms. Davis "Joel" wrote: 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. "Pinkiredd" wrote: 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? -- Ms. Davis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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 On Oct 14, 1:22*pm, Pinkiredd wrote: 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? -- Ms. Davis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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 "Pinkiredd" wrote: 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. -- Ms. Davis "Joel" wrote: 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. "Pinkiredd" wrote: 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? -- Ms. Davis |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging Files using Employe ID Number
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 "JP" wrote: 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 On Oct 14, 1:22 pm, Pinkiredd wrote: 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? -- Ms. Davis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging 2 files | Excel Discussion (Misc queries) | |||
Merging XLS Files | Excel Programming | |||
merging several files into one | Excel Programming | |||
Merging files | Excel Programming | |||
Merging Files | Excel Programming |