Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching records (Excel 2007)
I have two text files:
FILE-1 has 2,500 rows, each cell contains a nine-digit number. Examples: 391300004 453140065 672260027 FILE-2 has 1 million rows, each containing two or three nine-digit numbers. Examples: 391300004 391140021 391300004 391140021 391411001 391300004 391140021 391411002 453140065 453741001 453140065 453741001 460191001 453140065 453741002 453140065 453741002 460191002 453140065 453741003 672260014 672260046 672822058 672260014 672260046 672822059 672260017 672260041 672260017 672260041 673020006 672260017 672260042 672260017 672260042 672260047 672260027 672260046 672260027 672260046 672811001 672260027 672260046 672811002 I need to extract the row(s) in FILE-2 whose first nine-digit number matches the nine-digit number in FILE-1. Can someone provide step-by-step instructions on how I can do this? (Would it be easier to put the data in FILE-1 into a new column in FILE-2?) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching records (Excel 2007)
See my reply in your other post.
Regards, OssieMac "gary" wrote: I have two text files: FILE-1 has 2,500 rows, each cell contains a nine-digit number. Examples: 391300004 453140065 672260027 FILE-2 has 1 million rows, each containing two or three nine-digit numbers. Examples: 391300004 391140021 391300004 391140021 391411001 391300004 391140021 391411002 453140065 453741001 453140065 453741001 460191001 453140065 453741002 453140065 453741002 460191002 453140065 453741003 672260014 672260046 672822058 672260014 672260046 672822059 672260017 672260041 672260017 672260041 673020006 672260017 672260042 672260017 672260042 672260047 672260027 672260046 672260027 672260046 672811001 672260027 672260046 672811002 I need to extract the row(s) in FILE-2 whose first nine-digit number matches the nine-digit number in FILE-1. Can someone provide step-by-step instructions on how I can do this? (Would it be easier to put the data in FILE-1 into a new column in FILE-2?) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching records (Excel 2007)
The code put the data in a summary sheet in the 2nd workbook. Modify the 5
constants below to get the cod eto work. Book1 is File 1 and Book2 is File 2. Sub findmatch() Const wbk1 = "Book1" Const wbk2 = "Book2" Const wbk1data = "Sheet1" Const wbk2data = "Sheet1" Const wbk2summary = "Sheet2" With Workbooks(wbk1).Sheets(wbk1data) Wbk1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row End With With Workbooks(wbk2).Sheets(wbk2data) SummaryRowCount = 1 Wbk2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For Wbk2RowCount = 1 To Wbk2LastRow Wbk2SearchNum = Val(.Cells(Wbk2RowCount, "A")) For Wbk1RowCount = 1 To Wbk1LastRow With Workbooks(wbk1).Sheets(wbk1data) Wbk1SearchNum = Val(.Cells(Wbk1RowCount, "A")) End With If Wbk1SearchNum = Wbk2SearchNum Then .Rows(Wbk2RowCount).Copy _ Destination:= _ Workbooks(wbk2).Sheets(wbk2summary). _ Rows(SummaryRowCount) SummaryRowCount = SummaryRowCount + 1 Exit For End If Next Wbk1RowCount Next Wbk2RowCount End With End Sub gary wrote: I have two text files: FILE-1 has 2,500 rows, each cell contains a nine-digit number. Examples: 391300004 453140065 672260027 FILE-2 has 1 million rows, each containing two or three nine-digit numbers. Examples: 391300004 391140021 391300004 391140021 391411001 391300004 391140021 391411002 453140065 453741001 453140065 453741001 460191001 453140065 453741002 453140065 453741002 460191002 453140065 453741003 672260014 672260046 672822058 672260014 672260046 672822059 672260017 672260041 672260017 672260041 673020006 672260017 672260042 672260017 672260042 672260047 672260027 672260046 672260027 672260046 672811001 672260027 672260046 672811002 I need to extract the row(s) in FILE-2 whose first nine-digit number matches the nine-digit number in FILE-1. Can someone provide step-by-step instructions on how I can do this? (Would it be easier to put the data in FILE-1 into a new column in FILE-2?) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching records in two lists | Excel Discussion (Misc queries) | |||
Matching and Consolidating records | Excel Discussion (Misc queries) | |||
Labels: Unable to import all records Excel 2007 to Word 2007 Mailm | Excel Discussion (Misc queries) | |||
Matching Records | Excel Worksheet Functions | |||
Matching records | Excel Discussion (Misc queries) |