Can Excel merge like this?

[Sherry]

This code below reads 2 files and figures out that B2 in one file may or may
not match something in column CN in the other file. I don't really
understand how it works - some kind person here gave me the code. If the
macro finds a match it flags it by entering text 8 columns away from Column
CN. My problem is it only does this for one match. If mutiple records match
only one is flagged and the others get no flag. Can someone tel me how to
make it flag ALL records that match? It would make a nice Christmas present
for me.

Thanks


Sub MergeIt(File1, File2)
For Each strname In Workbooks(File1).Sheets("Sheet1").Range("b2:b800")
With Workbooks(File2).Sheets("Sheet1").Range("cn2:cn10000")
Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.Offset(0, 8) = "This one Matches"
End If
End With
Next
End Sub

 

[Kevin Stecyk]

Hi Sherry,

Good news and bad news.

Good news, I got it to mostly work. Bad news, it is not perfect.

If you have an identical 2 "key" values on after the other in column CN,
this routine will only identify the first key value.

For example, let's say the macro was trying to match the value 20 in
CN2:C10000. Further, let's assume C10=20, and C11=20. This routine will
only pick up C10, and not C11. I don't know why that is. I am hoping
someone else picks up this thread and solves the issue.

Continuing this example, the routine will search C11:C10000, but for some
reason it does not acknowledge the 20 in the first cell, namely C11. I
tried single stepping through this with debug statements, and was unable to
figure it out. If anyone wants my sample spreadsheets, please let me know
as to how I can send them to you.

Sherry, so while this solution is not perfect, I am hoping it inspires
someone to say, "ah Kevin, you missed ..." and the solution will be evident.

If you have very few repeats, then this will help you. But using my XL
2000, I had trouble with the duplicate values occuring in a contigous range.

Here is the routine below. Be careful of the line wrap!

Regards,
Kevin



Sub MergeIt3(File1 As String, File2 As String)

Dim rnCell1 As Range '\cell in first book
Dim rnCell2 As Range '\cell in second book
Dim iCount1 As Integer '\an offset count for col in 2nd book


For Each rnCell1 In Workbooks(File1).Sheets("Sheet1").Range("b2:b3")
iCount1 = 1
Do
With
Workbooks(File2).Sheets("Sheet1").Range("cn2:cn10000").Offset(iCount1 - 1,
0).Resize(10000 - iCount1, 1)
Set rnCell2 = .Find(rnCell1.Value, LookIn:=xlValues,
lookat:=xlWhole)
If Not rnCell2 Is Nothing Then
rnCell2.Offset(0, 8) = "This one Matches"
iCount1 = rnCell2.Row
End If
End With
Loop Until rnCell2 Is Nothing
Next

End Sub

 

[Kevin Stecyk]

Caught 2 typos: (a) on-->one...If you have an identical 2 "key" values one
after the other and (b) b2:b3-->b3:b800.

I hope too that the code is more readily understood with the appropriate
line breaks.

Thanks

~~~~~~~~~~~~~~~~~~
Hi Sherry,

Good news and bad news.

Good news, I got it to mostly work. Bad news, it is not perfect.

If you have an identical 2 "key" values one after the other in column CN,
this routine will only identify the first key value.

For example, let's say the macro was trying to match the value 20 in
CN2:C10000. Further, let's assume C10=20, and C11=20. This routine will
only pick up C10, and not C11. I don't know why that is. I am hoping
someone else picks up this thread and solves the issue.

Continuing this example, the routine will search C11:C10000, but for some
reason it does not acknowledge the 20 in the first cell, namely C11. I
tried single stepping through this with debug statements, and was unable to
figure it out. If anyone wants my sample spreadsheets, please let me know
as to how I can send them to you.

Sherry, so while this solution is not perfect, I am hoping it inspires
someone to say, "ah Kevin, you missed ..." and the solution will be evident.

If you have very few repeats, then this will help you. But using my XL
2000, I had trouble with the duplicate values occuring in a contigous range.

Here is the routine below. Be careful of the line wrap!

Regards,
Kevin

Sub MergeIt3(File1 As String, File2 As String)

Dim rnCell1 As Range '\cell in first book
Dim rnCell2 As Range '\cell in second book
Dim iCount1 As Integer '\an offset count for col in 2nd book


For Each rnCell1 In Workbooks(File1).Sheets("Sheet1").Range("b2:b800")
iCount1 = 1
Do
With Workbooks(File2).Sheets("Sheet1").Range("cn2:cn10000"). _
Offset(iCount1 - 1, 0).Resize(10000 - iCount1, 1)
Set rnCell2 = .Find(rnCell1.Value, LookIn:=xlValues, _
lookat:=xlWhole)
If Not rnCell2 Is Nothing Then
rnCell2.Offset(0, 8) = "This one Matches"
iCount1 = rnCell2.Row
End If
End With
Loop Until rnCell2 Is Nothing
Next

End Sub

 

[Sherry]

If it only picks up the first match it has the same problem as the macro I
was trying to use. I'm not smart enough to figure out why either of these
macros don't merge everything. Is anybody else?

 

[Kevin Stecyk]

Hi Sherry,

Yes, I was hoping that someone would jump in as well and assist. Let's see
if someone jumps soon.

I am puzzled too, so this would be a good learning exercise for me.

Regards,
Kevin

 

[Dave Peterson]

Are you checking to see how many matches are found in the CN Column?

If yes, then you could do it without a macro.

Just put a formula in a helper cell (and copy down):

=IF(CN2=[book1.xls]Sheet1!$B$2,"this one matches","this one doesn't match")

(or do I have it backwards?)

 

[Stephen Bullen]

Hi Sherry,

This code below reads 2 files and figures out that B2 in one file may or may
not match something in column CN in the other file. I don't really
understand how it works - some kind person here gave me the code. If the
macro finds a match it flags it by entering text 8 columns away from Column
CN. My problem is it only does this for one match. If mutiple records match
only one is flagged and the others get no flag. Can someone tel me how to
make it flag ALL records that match? It would make a nice Christmas present
for me.

Thanks

Sub MergeIt(File1, File2)
For Each strname In Workbooks(File1).Sheets("Sheet1").Range("b2:b800")
With Workbooks(File2).Sheets("Sheet1").Range("cn2:cn10000")
Set c = .Find(strname, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.Offset(0, 8) = "This one Matches"
End If
End With
Next
End Sub

I would do it with a worksheet function:

1. Select CV2 in File2
2. Type in the formula:

=IF(ISERROR(MATCH(CN2,[File1.xls]Sheet1!$B$2:$B$800,0)),"","This one matches")

3. Copy that down to the rest of the data and recalc.
4. Optionally copy the column and paste special > Values to convert the
formulae to values.

If you have to use a macro, just record yourself typing in that formula.


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Kevin Stecyk]

Sherry and Stephen,

Bill Manville answered my question in a CompuServe forum. As it turned out,
both Stephen and Bill noted my question and provided similar answers. I
hope Sherry you are still following this thread. If you want a VBA
solution, here is one below. It is essentially Stephen's solution, but put
into VBA form by Bill Manville.

Hope that helps.

Regards,
Kevin


Sub MergeIt(File1 as String, File2 as String)

With Workbooks(File2).Sheets("Sheet1").Range("cn2:cn10000")
.Offset(0, 8).FormulaR1C1 = "=IF(ISNA(MATCH(RC[-8],[" & File1 _
& "]Sheet1!R2C2:R800C2,0)),"""",""This one Matches"")"
End With

End Sub

<snip>

Stephen wrote...