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?)

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?)

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?)