Solved: Matching 2 customer record sheets

[zaboo9]

I have created 5 excel sheets to help in my explanation, they are:
OldInfo: This is data currently in my database
NewInfo: Data received from a purchase of a company that I need to merge
MatchedResults: A new sheet created from OldInfo and NewInfo that shows how I want matches to appear.
UnmatchedResultsOldInfo: This is the OldInfo data with the matched rows taken out
UnmatchedResultsFromNewInfo: This is the NewInfo data with the matched rows taken out

My company purchased another company, and I need to merge records of businesses that both companies have in their databases. The only guaranteed unique identifier is the “MJ Business Number,” this is given to companies in my industry and a company can only have one of these numbers. I ONLY want to match based on the MJ Business Number at this point.

I want the NewInfo to be compared against the OldInfo information, and when a match is found, I want the matched information from both data sheets placed into the “MatchedResults” sheet as shown in the MatchedResults file. I want the matched items taken out of the NewInfo and OldInfo files, and when the ALL matched files are taken out, I want the NewInfo saved as UnmatchedResultsFromNewInfo and OldInfo saved as UnmatchedResultsOldInfo.

In short, I want the information to appear as it does in the example files. Please let me know if there are any questions.
Thanks so much in advance for any help you can provide.

[Bob Phillips]

And what have you tried to date?

[zaboo9]

I am new at VBA, I am mediocore in JAVA, so I have been studying VBA and trying to learn it mostly. I have been able to do some sorting with vlookup, however I need to more fully automate this process because I am an intern and won't be at this company after this summer, and someone will need to continue this process. VBA appears to be the best way.
In short, I have spent weeks studying VBA and searching to find an answer, but I haven't yet attained the knowledge to complete the task, nor found code online to use or modify to solve the problem.

[GTO]

Just a sleepless stab...

Presuming a one-time deal, Old, New, and four add'l sheets in same wb. Alter header rows to match source data and format after done. I did not even attempt to split matching records to one sheet, I figure copy/paste must be good for something...

Option Explicit
 
Sub exa()
Dim _
OldInfo     As Object, _
NewInfo     As Object, _
a           As Variant, _
i           As Long
 
    Set OldInfo = DIC(shtOld.Range(shtOld.Range("A2"), shtOld.Cells(Rows.Count, "A").End(xlUp)))
    Set NewInfo = DIC(shtNew.Range(shtNew.Range("A2"), shtNew.Cells(Rows.Count, "A").End(xlUp)))
 
    For Each a In OldInfo
        i = i + 1
        If NewInfo.Exists(a) Then
            shtOld.Rows(i + 1).EntireRow.Copy shtMatchOld.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Else
            shtOld.Rows(i + 1).EntireRow.Copy shtNoMatchOld.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next
 
    i = 0
    For Each a In NewInfo
        i = i + 1
        If OldInfo.Exists(a) Then
            shtNew.Rows(i + 1).EntireRow.Copy ShtMatchNew.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Else
            shtNew.Rows(i + 1).EntireRow.Copy shtNoMatchNew.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next
End Sub
 
Function DIC(rng As Range) As Object
Dim rCell   As Range
Dim i       As Long
 
    Set DIC = CreateObject("Scripting.Dictionary")
 
    For Each rCell In rng
        i = i + 1
        DIC.Item(rCell.Value) = rCell.Value
    Next
End Function

Maybe?

Mark

PS - Please note, codenames used for ease...

[zaboo9]

GTO,
Thank you very much for the help. I now have two new problems I've encountered:
1. Sometimes the MJ Business Number will be blank (I didn't have any in the example that were blank...), and when that happens, the array/object that holds the list, it doesn't seem to be storing all of the blanks.

2. I added code to the method that checks for a blank, and when there is a blank it is supposed to copy the row and places it in the NoMatch_Old or NoMatch_New respectively; however, this isn't working because it is skipping some of the blanks due to it not being stored properly in the array/object.

I have posted the modified data set. Any ideas? I have spent 5-6 hours today on this, but couldn't figure out how to handle the blank spaces with If, else, elseif statements.
Thanks so much for all the help!

[GTO]

GTO,
Thank you very much for the help. I now have two new problems I've encountered:
1. Sometimes the MJ Business Number will be blank (I didn't have any in the example that were blank...), and when that happens, the array/object that holds the list, it doesn't seem to be storing all of the blanks.

2. I added code to the method that checks for a blank, and when there is a blank it is supposed to copy the row and places it in the NoMatch_Old or NoMatch_New respectively; however, this isn't working because it is skipping some of the blanks due to it not being stored properly in the array/object.

Hi again,

#1 would have been a good thing to have mentioned. The Dictionary will only store unique keys, which I was assuming that no MJ number will be listed twice, and that there would be no blanks. See, it would only store one blank, so we'd be toast there, but more importantly, I was just using the dictionary for the .Exists method.

As we may have blank vals in the MJ number column, at least for anything my poor brain could think of, dictionary no longer sounded very stellar.

If I have been paying good enough attention though, I think we can use the worksheet function MATCH in the code, in place of .Exists.

I ran out of time to verify results, but a quick check seemed promising.

In your wb from #5, try:

Option Explicit
    
Sub exa()
Dim _
rngOld      As Range, _
rngNew      As Range, _
a           As Variant, _
i           As Long
    
    '// Set a base range to search for the last row of data, as we cannot depend upon   //
    '// there being a MJ Num in the last record.                                        //
    Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(shtOld.Rows.Count, "H"))
    '// Set our range based on finding the last true row from above.                    //
    Set rngOld = shtOld.Range(shtOld.Range("A2"), _
                              shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))
    '//SAA
    Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(shtNew.Rows.Count, "D"))
    Set rngNew = shtNew.Range(shtNew.Range("A2"), _
                              shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A"))
    
    '// IF I have this right (a bit chancy at best), I think I am using Application.MATCH//
    '// to either find each val (or that is, the row in the array), which would return a//
    '// TRUE for NOT ISERROR, or, return a FALSE if not found or an error due to an     //
    '// empty string being supplied.                                                    //
    For i = 1 To UBound(rngOld.Value, 1)
        If Not IsError(Application.Match(rngOld.Value(xlRangeValueDefault)(i, 1), rngNew, 0)) Then
            rngOld.Cells(i).EntireRow.Copy _
              shtMatchOld.Cells( _
                RangeFound( _
                  shtMatchOld.Range(shtMatchOld.Range("A1"), _
                  shtMatchOld.Cells(shtMatchOld.Rows.Count, "H") _
                                ), , _
                    shtMatchOld.Cells(1) _
                           ).Row, 1).Offset(1)
        Else
            rngOld.Cells(i).EntireRow.Copy _
              shtNoMatchOld.Cells( _
                RangeFound(shtNoMatchOld.Range(shtNoMatchOld.Range("A1"), _
                shtNoMatchOld.Cells(shtNoMatchOld.Rows.Count, "H") _
                                  ), , shtNoMatchOld.Cells(1) _
                           ).Row, 1).Offset(1)
        End If
    Next
    
    For i = 1 To UBound(rngOld.Value, 1)
        If Not IsError(Application.Match(rngNew.Value(xlRangeValueDefault)(i, 1), rngOld, 0)) Then
            rngNew.Cells(i).EntireRow.Copy _
              ShtMatchNew.Cells( _
                RangeFound(ShtMatchNew.Range(ShtMatchNew.Range("A1"), _
                ShtMatchNew.Cells(ShtMatchNew.Rows.Count, "D") _
                                ), , ShtMatchNew.Cells(1) _
                           ).Row, 1).Offset(1)
        Else
            rngNew.Cells(i).EntireRow.Copy _
              shtNoMatchNew.Cells( _
                RangeFound(shtNoMatchNew.Range(shtNoMatchNew.Range("A1"), _
                shtNoMatchNew.Cells(shtNoMatchNew.Rows.Count, "D") _
                                  ), , shtNoMatchNew.Cells(1) _
                           ).Row, 1).Offset(1)
        End If
    Next
End Sub
    
Function RangeFound(SearchRange As Range, _
                    Optional FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

Mark

[GTO]

Ack. I forgot to mention that the destination sheets will require having at least one value in the header row...

[zaboo9]

The code worked on the sample data I sent you, but when I used it on the actual data, it started matching blank spaces. The actual data file is huge (65000 records on the Old sheet, and 1100 on the New), and there are more columns then I provided on the sample data.

I have attached another sheet (Thursday.xls) that has all of the columns but not all the rows (Since it is a huge file), and I just changed some data of the customers. The MJ number is actually 9 numbers long, so I included those as 9 digit numbers as well.

When the code is run on this Thursday.xls, it puts all but 1 blank into the NoMatch sheets (As you'll see when the code is run.) I looked through the code and tried to see if I could change it to make it so no blanks were matched by changing the
" Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(shtOld.Rows.Count, "H"))" and
Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(shtNew.Rows.Count, "D"))"
So that they go to the proper columns (Q and V) but I couldn't make it work.

Any suggestions? Thanks for the help again!

[zaboo9]

I found the problem, there were leading apostrophes in the "blank cells." I set the MJ number cells as Text, copied the MJ number cells, pasted them in Notepad, then copied the info in notedpad and pasted it back into Excel and the apostrophes are now gone. I'm re-running the code again, it takes about 2 hours. I'll let you know the outcome

[GTO]

I found the problem, there were leading apostrophes in the "blank cells." I set the MJ number cells as Text, copied the MJ number cells, pasted them in Notepad, then copied the info in notedpad and pasted it back into Excel and the apostrophes are now gone. I'm re-running the code again, it takes about 2 hours. I'll let you know the outcome

I am off to bed, so will have to look later. If I am reading your last two posts correctly at all, I think that running thru 65+k rows is not going to be blindingly quick no matter what. Again, tired so maybe not thinking so well, but I'd think that we may want to flip the ranges into arrays, if for nothing else, the existance test.

I'm not sure if Appllication.Match is the fastest way to check through an array that long either, but maybe?

I would also figure on coming up with some counters someways, as .Find that many times cannot be the best answer.

For the first step though, try this to dump the prefix character (right below where the ranges are finalized):

[vba]
rngOld.Value = rngOld.Value
rngNew.Value = rngNew.Value
[/vba]

Not well checked, but I think should work.

Mark

[zaboo9]

Thank you for all your help, GTO. Sorry for the long delay in posting, The test runs, but at the end it gives the error, "Subscript out of range." I now am testing the code, and have just taken a small portion of the sheet to test on since the whole file takes two hours.

I have found that when "Old" has the same or less number of rows than "New", the code runs fine. When I have more rows in "Old" than "New" I get the error "Run-time error '9': Subscript out of range." However, it looks like the code runs perfectly fine, and all the other sheets are filled with all of the proper information.

I have attached my test Excel sheet, in its current state it is receiving the "Run-time error '9': Subscript out of range" and it will give you the option to debug (which I tried, but don't really understand) If you delete one row from the "Old" tab, it will run perfect without errors.

If you have more rows on "New" then it will run without errors, but it won't look at the row numbers that "Old" doesn't have.

Any suggestions on what to do?

Note:
1. I have the MJ Number column in both "Old" and "New" as text, and I typed in the numbers so there is no apostrophe, I'll worry about the leading apostrophe later.
2. I am now using Excel 2007, I hope its an improvement!

[GTO]

Off to bed for me, but please edit the attachment or repost in .xls format. There are plenty (including yours truly) who have limited or no access to xlsm format.

[zaboo9]

Good morning! Here is the file in .xls, thanks again!

[zaboo9]

[VBA]

'// Set a base range to search for the last row of data, as we cannot depend upon //
'// there being a MJ Num in the last record. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(shtOld.Rows.Count, "H"))
'// Set our range based on finding the last true row from above. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), _
shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))
'//SAA
Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(shtNew.Rows.Count, "D"))
Set rngNew = shtNew.Range(shtNew.Range("A2"), _
shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A"))
[/VBA]

[zaboo9]

I'm trying to make the code below make it so it just knew the range by user input into input boxes (as shown below.) Any ideas on how to do this? I've only got the input boxes in this code.

[VBA]
Sub exa()
Dim _
rngOld As Range, _
rngNew As Range, _
a As Variant, _
i As Long, _
oldRows As Integer, _
newRows As Integer
oldRows = InputBox("How many rows in Old?")
newRows = InputBox("How many rows in New?")


'// Set a base range to search for the last row of data, as we cannot depend upon //
'// there being a MJ Num in the last record. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(shtOld.Rows.Count, "H"))
'// Set our range based on finding the last true row from above. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), _
shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))
'//SAA
Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(shtNew.Rows.Count, "D"))
Set rngNew = shtNew.Range(shtNew.Range("A2"), _
shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A"))
[/VBA]

[zaboo9]

It's done! Thank you so much for the help GTO, I was able to modify the code you gave me to give me exactly what I need. I added two input boxes to tell the amount of rows that are in "Old" and "New" and then add that to the range code, and then I deleted the loop through "New" because it was throwing an error (And I just need matches, so though a redunant test would be nice, it's not essential.)

Here's the final code:
[vba]Option Explicit
Sub exa()
Dim _
rngOld As Range, _
rngNew As Range, _
a As Variant, _
i As Long, _
oldRows As Integer, _
newRows As Integer
oldRows = InputBox("How many rows in Old?")
newRows = InputBox("How many rows in New?")


'// Set a base range to search for the last row of data, as we cannot depend upon //
'// there being a MJ Num in the last record. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(oldRows, "Q"))
'// Set our range based on finding the last true row from above. //
Set rngOld = shtOld.Range(shtOld.Range("A2"), _
shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))
'//SAA
Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(newRows, "V"))
Set rngNew = shtNew.Range(shtNew.Range("A2"), _
shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A"))

'// IF I have this right (a bit chancy at best), I think I am using Application.MATCH//
'// to either find each val (or that is, the row in the array), which would return a//
'// TRUE for NOT ISERROR, or, return a FALSE if not found or an error due to an //
'// empty string being supplied. //

'For i = 1 To UBound(rngOld.Value, 1)
For i = 1 To UBound(rngOld.Value, 1)
If Not IsError(Application.Match(rngOld.Value(xlRangeValueDefault)(i, 1), rngNew, 0)) Then
rngOld.Cells(i).EntireRow.Copy _
shtMatchOld.Cells( _
RangeFound( _
shtMatchOld.Range(shtMatchOld.Range("A1"), _
shtMatchOld.Cells(shtMatchOld.Rows.Count, "H") _
), , _
shtMatchOld.Cells(1) _
).Row, 1).Offset(1)
Else
rngOld.Cells(i).EntireRow.Copy _
shtNoMatchOld.Cells( _
RangeFound(shtNoMatchOld.Range(shtNoMatchOld.Range("A1"), _
shtNoMatchOld.Cells(shtNoMatchOld.Rows.Count, "H") _
), , shtNoMatchOld.Cells(1) _
).Row, 1).Offset(1)
End If
Next

End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function[/vba]


Thanks again!

[GTO]

Hi Zaboo,

Sorry for the delay in responding. I lost track of this for a bit.

I believe the error just to be a silly mistake on my part. At the start of the last loop, change:
[vba]
'//oversight: needs to run to upper bound of rngNew.Value
For i = 1 To UBound(rngNew.Value, 1)
[/vba]

I am not sure about post #14.

Reference #15: I'm not exactly sure how using an input box would help. I haven't thought through this, but it would seem to me that the search would no longer work properly, as it wouldn't look through all records.

Does that make sense, or am I missing something?

Finally - as I mentioned at #10, I think we can make this run well faster if we take just the values, rather than copying. I am also confident that we can use another method to better the speed over Application.Match while running through so many rows.

Before doing so, please refresh my memory and give me a bit of info:

1. The MJ number is the nearest thing we have to a unique identifier.
   1. If the MJ Number in the Old sheet is not found in the New sheet, we want it listed in the NoMatch Old sheet (and visa versa)
   2. The same holds true for where the MJ Number is missing.
2. What are the actual last columns in both the New and Old sheets? The example wb you have posted should be fine, but we want the find the last row searching all columns in the real wb that may have data.

Mark

[GTO]

OOPS.

I must have not checked before entering hitting the Post button. I am glad you got it working, and if it is satisfactory, that is fine.

See my comments at the last post though, as if you want further improvements, I think there are substantial ones to be made.

Mark

PS - you should be able to edit your last post. Surround the code with [vba]'...your code here...[/vba] tags and it will be much easier to read.

[zaboo9]

Thank you for this line of code! [vba] For i = 1 ToUBound(rngNew.Value, 1)[/vba] It solved that problem for me.

I’ll paste “final” code I have below (including a code to clear the result sheets, this makes it much easier during testing.) I think it all works, but if you could go over it and see if you see anything that isn’t working, that would be great.

In answer to your question:

I'm not exactly sure how using an input box would help. I haven't thought through this, but it would seem to me that the search would no longer work properly, as it wouldn't look through all records.
 
Does that make sense, or am I missing something? 

What I have done is made it so that the input boxes will have the user input the amount of rows in “Old” (In JulyFourteenTest.xls it is 1500) and in “New” (100) and so it won’t need to even search for the last row, it will just already know the range. There can be blank cells, and it appears to still work.

In the following code, all I did was set the variable that holds the rows in the following places that I have bolded[vba] '// Set a base range to search for the last row of data, as we cannot depend upon[/vba][vba]
'// there being a MJ Num in the last record.
Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(oldRows, "Q '// Set our range based on finding the last true row from above. //[/font]
Set rngOld = shtOld.Range(shtOld.Range("A2"), _[/font]
shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))
'//SAA
Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(newRows, "V"))
Set rngNew = shtNew.Range(shtNew.Range("A2"), _[/font]
shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A")) [/vba]
The speed of the process doesn’t matter to me, it will only run through the 100,000+ record list once a month, and it has taken between 2-3 hours, which isn’t a big deal. The only thing that matters is that the data returned is accurate.

[FONT='Verdana','sans-serif'][vba][/font][/vba][vba][FONT='Verdana','sans-serif']Option Explicit[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif']Sub modifiedExa()[/font]
[FONT='Verdana','sans-serif']Dim _[/font]
[FONT='Verdana','sans-serif']rngOld As Range, _[/font]
[FONT='Verdana','sans-serif']rngNew As Range, _[/font]
[FONT='Verdana','sans-serif']a As Variant, _[/font]
[FONT='Verdana','sans-serif']i As Long, _[/font]
[FONT='Verdana','sans-serif']oldRows As Long, _[/font]
[FONT='Verdana','sans-serif']newRows As Long[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif']oldRows = InputBox("How many rows in Old?")[/font]
[FONT='Verdana','sans-serif']newRows = InputBox("How many rows in New?")[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] '// Set a base range to search for the last row of data, as we cannot depend upon //[/font]
[FONT='Verdana','sans-serif'] '// there being a MJ Num in the last record. //[/font]
[FONT='Verdana','sans-serif'] Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(oldRows, "Q"))[/font]
[FONT='Verdana','sans-serif'] '// Set our range based on finding the last true row from above. //[/font]
[FONT='Verdana','sans-serif'] Set rngOld = shtOld.Range(shtOld.Range("A2"), _[/font]
[FONT='Verdana','sans-serif'] shtOld.Cells(RangeFound(rngOld, , rngOld.Cells(1)).Row, "A"))[/font]
[FONT='Verdana','sans-serif'] '//SAA[/font]
[FONT='Verdana','sans-serif'] Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(newRows, "V"))[/font]
[FONT='Verdana','sans-serif'] Set rngNew = shtNew.Range(shtNew.Range("A2"), _[/font]
[FONT='Verdana','sans-serif'] shtNew.Cells(RangeFound(rngNew, , rngNew.Cells(1)).Row, "A"))[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] '// IF I have this right (a bit chancy at best), I think I am using Application.MATCH//[/font]
[FONT='Verdana','sans-serif'] '// to either find each val (or that is, the row in the array), which would return a//[/font]
[FONT='Verdana','sans-serif'] '// TRUE for NOT ISERROR, or, return a FALSE if not found or an error due to an //[/font]
[FONT='Verdana','sans-serif'] '// empty string being supplied. //[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] 'For i = 1 To UBound(rngOld.Value, 1)[/font]
[FONT='Verdana','sans-serif'] For i = 1 To UBound(rngOld.Value, 1)[/font]
[FONT='Verdana','sans-serif'] If Not IsError(Application.Match(rngOld.Value(xlRangeValueDefault)(i, 1), rngNew, 0)) Then[/font]
[FONT='Verdana','sans-serif'] rngOld.Cells(i).EntireRow.Copy _[/font]
[FONT='Verdana','sans-serif'] shtMatchOld.Cells( _[/font]
[FONT='Verdana','sans-serif'] RangeFound( _[/font]
[FONT='Verdana','sans-serif'] shtMatchOld.Range(shtMatchOld.Range("A1"), _[/font]
[FONT='Verdana','sans-serif'] shtMatchOld.Cells(shtMatchOld.Rows.Count, "H") _[/font]
[FONT='Verdana','sans-serif'] ), , _[/font]
[FONT='Verdana','sans-serif'] shtMatchOld.Cells(1) _[/font]
[FONT='Verdana','sans-serif'] ).Row, 1).Offset(1)[/font]
[FONT='Verdana','sans-serif'] Else[/font]
[FONT='Verdana','sans-serif'] rngOld.Cells(i).EntireRow.Copy _[/font]
[FONT='Verdana','sans-serif'] shtNoMatchOld.Cells( _[/font]
[FONT='Verdana','sans-serif'] RangeFound(shtNoMatchOld.Range(shtNoMatchOld.Range("A1"), _[/font]
[FONT='Verdana','sans-serif'] shtNoMatchOld.Cells(shtNoMatchOld.Rows.Count, "H") _[/font]
[FONT='Verdana','sans-serif'] ), , shtNoMatchOld.Cells(1) _[/font]
[FONT='Verdana','sans-serif'] ).Row, 1).Offset(1)[/font]
[FONT='Verdana','sans-serif'] End If[/font]
[FONT='Verdana','sans-serif'] Next[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] '//oversight: needs to run to upper bound of rngNew.Value[/font]
[FONT='Verdana','sans-serif']For i = 1 To UBound(rngNew.Value, 1)[/font]
[FONT='Verdana','sans-serif'] If Not IsError(Application.Match(rngNew.Value(xlRangeValueDefault)(i, 1), rngOld, 0)) Then[/font]
[FONT='Verdana','sans-serif'] rngNew.Cells(i).EntireRow.Copy _[/font]
[FONT='Verdana','sans-serif'] shtMatchNew.Cells( _[/font]
[FONT='Verdana','sans-serif'] RangeFound(shtMatchNew.Range(shtMatchNew.Range("A1"), _[/font]
[FONT='Verdana','sans-serif'] shtMatchNew.Cells(shtMatchNew.Rows.Count, "D") _[/font]
[FONT='Verdana','sans-serif'] ), , shtMatchNew.Cells(1) _[/font]
[FONT='Verdana','sans-serif'] ).Row, 1).Offset(1)[/font]
[FONT='Verdana','sans-serif'] Else[/font]
[FONT='Verdana','sans-serif'] rngNew.Cells(i).EntireRow.Copy _[/font]
[FONT='Verdana','sans-serif'] shtNoMatchNew.Cells( _[/font]
[FONT='Verdana','sans-serif'] RangeFound(shtNoMatchNew.Range(shtNoMatchNew.Range("A1"), _[/font]
[FONT='Verdana','sans-serif'] shtNoMatchNew.Cells(shtNoMatchNew.Rows.Count, "D") _[/font]
[FONT='Verdana','sans-serif'] ), , shtNoMatchNew.Cells(1) _[/font]
[FONT='Verdana','sans-serif'] ).Row, 1).Offset(1)[/font]
[FONT='Verdana','sans-serif'] End If[/font]
[FONT='Verdana','sans-serif'] Next[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif']End Sub[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif']Function RangeFound(SearchRange As Range, _[/font]
[FONT='Verdana','sans-serif'] Optional FindWhat As String = "*", _[/font]
[FONT='Verdana','sans-serif'] Optional StartingAfter As Range, _[/font]
[FONT='Verdana','sans-serif'] Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _[/font]
[FONT='Verdana','sans-serif'] Optional LookAtWholeOrPart As XlLookAt = xlPart, _[/font]
[FONT='Verdana','sans-serif'] Optional SearchRowCol As XlSearchOrder = xlByRows, _[/font]
[FONT='Verdana','sans-serif'] Optional SearchUpDn As XlSearchDirection = xlPrevious, _[/font]
[FONT='Verdana','sans-serif'] Optional bMatchCase As Boolean = False) As Range[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] If StartingAfter Is Nothing Then[/font]
[FONT='Verdana','sans-serif'] Set StartingAfter = SearchRange(1)[/font]
[FONT='Verdana','sans-serif'] End If[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif'] Set RangeFound = SearchRange.Find(What:=FindWhat, _[/font]
[FONT='Verdana','sans-serif'] After:=StartingAfter, _[/font]
[FONT='Verdana','sans-serif'] LookIn:=LookAtTextOrFormula, _[/font]
[FONT='Verdana','sans-serif'] LookAt:=LookAtWholeOrPart, _[/font]
[FONT='Verdana','sans-serif'] SearchOrder:=SearchRowCol, _[/font]
[FONT='Verdana','sans-serif'] SearchDirection:=SearchUpDn, _[/font]
[FONT='Verdana','sans-serif'] MatchCase:=bMatchCase)[/font]
[FONT='Verdana','sans-serif']End Function[/font]
[FONT='Verdana','sans-serif'] [/font]
[FONT='Verdana','sans-serif']Sub clear()[/font]
[FONT='Verdana','sans-serif']'[/font]
[FONT='Verdana','sans-serif']' clear Macro[/font]
[FONT='Verdana','sans-serif']'[/font]
[FONT='Verdana','sans-serif']' Keyboard Shortcut: Ctrl+g[/font]
[FONT='Verdana','sans-serif']'[/font]
[FONT='Verdana','sans-serif'] Sheets("NoMatch_Old").Select[/font]
[FONT='Verdana','sans-serif'] Range("A2:R3000").Select[/font]
[FONT='Verdana','sans-serif'] Range("R2").Activate[/font]
[FONT='Verdana','sans-serif'] Selection.ClearContents[/font]
[FONT='Verdana','sans-serif'] Sheets("Match_New").Select[/font]
[FONT='Verdana','sans-serif'] Range("A2:V3000").Select[/font]
[FONT='Verdana','sans-serif'] Range("V2").Activate[/font]
[FONT='Verdana','sans-serif'] Selection.ClearContents[/font]
[FONT='Verdana','sans-serif'] Sheets("Match_Old").Select[/font]
[FONT='Verdana','sans-serif'] Range("A2:R3000").Select[/font]
[FONT='Verdana','sans-serif'] Range("R2").Activate[/font]
[FONT='Verdana','sans-serif'] Selection.ClearContents[/font]
[FONT='Verdana','sans-serif'] Sheets("NoMatch_New").Select[/font]
[FONT='Verdana','sans-serif'] Range("A2:V3000").Select[/font]
[FONT='Verdana','sans-serif'] Range("V2").Activate[/font]
[FONT='Verdana','sans-serif'] Selection.ClearContents[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 14[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 13[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 12[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 11[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 10[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 9[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 7[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 6[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 5[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 3[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 2[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif'] Sheets("Match_New").Select[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 14[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 13[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 12[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 10[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 9[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 7[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 6[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 4[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 3[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 2[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif'] Sheets("NoMatch_Old").Select[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 10[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 6[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 4[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 2[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif'] Sheets("Match_Old").Select[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 10[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 7[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 5[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 3[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollColumn = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif'] Sheets("New").Select[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 12[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 11[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 10[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 9[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 7[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 6[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 5[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 4[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 3[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 2[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif'] Sheets("Old").Select[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 9[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 8[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 7[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 6[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 5[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 4[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 3[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 2[/font]
[FONT='Verdana','sans-serif'] ActiveWindow.ScrollRow = 1[/font]
[FONT='Verdana','sans-serif'] Range("A2").Select[/font]
[FONT='Verdana','sans-serif']End Sub[/font]
[FONT='Verdana','sans-serif'][/vba][/font]

I created test variables in “New” which areTest10, Test20…..Test100 and put those throughout my “Old.” To keep the file under 1 MB, I kept the data set relatively small. I then went through old and made blank spaces. The numbers in old aren’t random, but when you test the program you can make up MJ Numbers in “New” and place them anywhere in “Old” and you should see them in both “Match_Old” and “Match_New.”

Hopefully this all makes sense. Let me know if you have any questions, and also if the data returned appears to be accurate.
Thanks!

[GTO]

Greetings,

In no particular order...

Up at the top, where we are declaring/dimensioning variables, ditch the 'a', it was just a temp variable.

I’ll paste final code I have below (including a code to clear the result sheets, this makes it much easier during testing.) I think it all works, but if you could go over it and see if you see anything that isn’t working, that would be great.

I believe you'll want to read through the code a bit more thoroughly. You currently have it checking for the bottom row, using columns 'Q' and 'V' as the last columns in the first lines of the code. Your example shows column R and V as the last columns.

Also, if you elect this as the method to use, you'll want to update similarly in the 'For i = 1 to Ubound(...' loops. If you step through the code, it may be easier to discern what is happening, but in short, we are describing the range to look for the last row in.

]I'm not exactly sure how using an input box would help. I haven't thought through this, but it would seem to me that the search would no longer work properly...

What I have done is made it so that the input boxes will have the user input the amount of rows in "Old" (In JulyFourteenTest.xls it is 1500) and in "New" (100) and so it won’t need to even search for the last row, it will just already know the range. There can be blank cells, and it appears to still work.

Okay - let me modify my statement a bit. Certainly we could allow the user to set the value for the last row. So far though, I stand by that we would not want to. We are only saving two quick searches, both of which will happen much faster programattically than filling in the input boxes. My real concern however, is that should the user think it would be okay to do a 'partial', it invalidates the searching with Application.Match and hence, would return bad data.

My former comments reference searching and copying being slow, specific to searching, were reference the searching for the last row in the appropriate destination sheet, before ea row is copied over.

I created test variables in "New" which areTest10, Test20..Test100 and put those throughout my "Old." To keep the file under 1 MB, I kept the data set relatively small. I then went through old and made blank spaces. The numbers in old aren’t random, but when you test the program you can make up MJ Numbers in "New" and place them anywhere in "Old" and you should see them in both "Match_Old" and "Match_New."

Hopefully this all makes sense. Let me know if you have any questions, and also if the data returned appears to be accurate.

Thanks!

I am guessing that you were going to post a larger example but did not. Using the wb at Post#13, a quck check, but it seemed accurate.

I did catch that as its once a month, speed was not a concern. That said, 100,000 records means 100,000 searches for a new bottom row, and 100,000 uses of Application.Match, ea use of which is running through a butt load of rows. So... would you mind trying this as an alternate? Please test against a junk copy of a full set of records. While not perfect, and you could re-use some of the variables within, I wrote in a hopefully easy to follow manner. I think that you will see a notable improvement.

Option Explicit
    
Sub exa2()
Dim _
aryOldVals          As Variant, NewDIC              As Object, _
aryNewVals          As Variant, OldDIC              As Object, _
aryMatchNew         As Variant, rngCol              As Range, _
aryMatchOld         As Variant, rngRow              As Range, _
aryNoMatchNew       As Variant, rngOld              As Range, _
aryNoMatchOld       As Variant, rngNew              As Range, _
aryMatchNewOutput   As Variant, rngNewMatch         As Range, _
aryNoMatchNewOutput As Variant, rngNewNoMatch       As Range, _
aryMatchOldOutput   As Variant, rngOldMatch         As Range, _
aryNoMatchOldOutput As Variant, rngOldNoMatch       As Range, _
x                   As Long, y                      As Long, _
i                   As Long, laryCol                As Long, _
laryRow             As Long, laryRow2               As Long
    
    
    Set rngCol = RangeFound(SearchRange:= _
                              shtOld.Range( _
                                shtOld.Range("A2"), _
                                shtOld.Cells(shtOld.Rows.Count, shtOld.Columns.Count) _
                                           ), _
                            StartingAfter:=shtOld.Range("A2"), _
                            SearchRowCol:=xlByColumns)
    
    Set rngRow = RangeFound(SearchRange:= _
                              shtOld.Range( _
                                shtOld.Range("A2"), _
                                shtOld.Cells(shtOld.Rows.Count, shtOld.Columns.Count) _
                                           ), _
                            StartingAfter:=shtOld.Range("A2"), _
                            SearchRowCol:=xlByRows)
    
    Set rngOld = shtOld.Range(shtOld.Range("A2"), shtOld.Cells(rngRow.Row, rngCol.Column))
    
    Set rngCol = RangeFound(SearchRange:= _
                              shtNew.Range( _
                                shtNew.Range("A2"), _
                                shtNew.Cells(shtNew.Rows.Count, shtNew.Columns.Count) _
                                           ), _
                            StartingAfter:=shtNew.Range("A2"), _
                            SearchRowCol:=xlByColumns)
    
    Set rngRow = RangeFound(SearchRange:= _
                              shtNew.Range( _
                                shtNew.Range("A2"), _
                                shtNew.Cells(shtNew.Rows.Count, shtNew.Columns.Count) _
                                           ), _
                            StartingAfter:=shtNew.Range("A2"), _
                            SearchRowCol:=xlByRows)
    
    Set rngNew = shtNew.Range(shtNew.Range("A2"), shtNew.Cells(rngRow.Row, rngCol.Column))
    
    aryOldVals = rngOld.Value
    aryNewVals = rngNew.Value
    
    Set NewDIC = CreateObject("Scripting.Dictionary")
    Set OldDIC = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(aryOldVals, 1)
        If Not aryOldVals(i, 1) = Empty Then
            OldDIC.Item(aryOldVals(i, 1)) = aryOldVals(i, 1)
        End If
    Next
    
    For i = 1 To UBound(aryNewVals)
        If Not aryNewVals(i, 1) = Empty Then
            NewDIC.Item(aryNewVals(i, 1)) = aryNewVals(i, 1)
        End If
    Next
    
    ReDim aryMatchNew(1 To UBound(aryNewVals, 2), 1 To UBound(aryNewVals, 1))
    ReDim aryNoMatchNew(1 To UBound(aryNewVals, 2), 1 To UBound(aryNewVals, 1))
    ReDim aryMatchOld(1 To UBound(aryOldVals, 2), 1 To UBound(aryOldVals, 1))
    ReDim aryNoMatchOld(1 To UBound(aryOldVals, 2), 1 To UBound(aryOldVals, 1))
    
    laryCol = 0: laryRow = 0: laryRow2 = 0
    For i = 1 To UBound(aryOldVals, 1)
        If NewDIC.Exists(aryOldVals(i, 1)) Then
            laryRow = laryRow + 1
            For laryCol = 1 To UBound(aryOldVals, 2)
                aryMatchOld(laryCol, laryRow) = aryOldVals(i, laryCol)
            Next
        Else
            laryRow2 = laryRow2 + 1
            For laryCol = 1 To UBound(aryOldVals, 2)
                aryNoMatchOld(laryCol, laryRow2) = aryOldVals(i, laryCol)
            Next
            
        End If
    Next
    
    laryCol = 0: laryRow = 0: laryRow2 = 0
    For i = 1 To UBound(aryNewVals, 1)
        If OldDIC.Exists(aryNewVals(i, 1)) Then
            laryRow = laryRow + 1
            For laryCol = 1 To UBound(aryNewVals, 2)
                aryMatchNew(laryCol, laryRow) = aryNewVals(i, laryCol)
            Next
        Else
            laryRow2 = laryRow2 + 1
            For laryCol = 1 To UBound(aryNewVals, 2)
                aryNoMatchNew(laryCol, laryRow2) = aryNewVals(i, laryCol)
            Next
            
        End If
    Next
        
    ReDim Preserve aryMatchOld(1 To UBound(aryOldVals, 2), 1 To laryRow)
    ReDim Preserve aryNoMatchOld(1 To UBound(aryOldVals, 2), 1 To laryRow2)
    ReDim Preserve aryMatchNew(1 To UBound(aryNewVals, 2), 1 To laryRow)
    ReDim Preserve aryNoMatchNew(1 To UBound(aryNewVals, 2), 1 To laryRow2)
    
    ReDim aryMatchNewOutput(1 To UBound(aryMatchNew, 2), 1 To UBound(aryMatchNew, 1))
    ReDim aryNoMatchNewOutput(1 To UBound(aryNoMatchNew, 2), 1 To UBound(aryNoMatchNew, 1))
    ReDim aryMatchOldOutput(1 To UBound(aryMatchOld, 2), 1 To UBound(aryMatchOld, 1))
    ReDim aryNoMatchOldOutput(1 To UBound(aryNoMatchOld, 2), 1 To UBound(aryNoMatchOld, 1))
    
    For x = 1 To UBound(aryMatchNewOutput, 1)
        For y = 1 To UBound(aryMatchNewOutput, 2)
            aryMatchNewOutput(x, y) = aryMatchNew(y, x)
        Next
    Next
    
    For x = 1 To UBound(aryNoMatchNewOutput, 1)
        For y = 1 To UBound(aryNoMatchNewOutput, 2)
            aryNoMatchNewOutput(x, y) = aryNoMatchNew(y, x)
        Next
    Next
    
    For x = 1 To UBound(aryMatchOldOutput, 1)
        For y = 1 To UBound(aryMatchOldOutput, 2)
            aryMatchOldOutput(x, y) = aryMatchOld(y, x)
        Next
    Next
    
    For x = 1 To UBound(aryNoMatchOldOutput, 1)
        For y = 1 To UBound(aryNoMatchOldOutput, 2)
            aryNoMatchOldOutput(x, y) = aryNoMatchOld(y, x)
        Next
    Next
    
    Set rngNewMatch = _
        shtMatchNew.Range("A2").Resize(UBound(aryMatchNewOutput, 1), _
                                       UBound(aryMatchNewOutput, 2))
    Set rngNewNoMatch = _
        shtNoMatchNew.Range("A2").Resize(UBound(aryNoMatchNewOutput, 1), _
                                         UBound(aryNoMatchNewOutput, 2))
    Set rngOldMatch = _
        shtMatchOld.Range("A2").Resize(UBound(aryMatchOldOutput, 1), _
                                       UBound(aryMatchOldOutput, 2))
    Set rngOldNoMatch = _
        shtNoMatchOld.Range("A2").Resize(UBound(aryNoMatchOldOutput, 1), _
                                         UBound(aryNoMatchOldOutput, 2))
    With rngNewMatch
        .Value = aryMatchNewOutput
        '// pretty up interior, borders, etc here.
    End With
    
    rngNewNoMatch.Value = aryNoMatchNewOutput
    rngOldMatch.Value = aryMatchOldOutput
    rngOldNoMatch.Value = aryNoMatchOldOutput
End Sub

Hope that helps,

Mark

PS - For the clearing cells between tests bit, once you have recorded a macro, you can generally see what belongs to what , and ditch quite a bit of unnecessary stuff
[vba]Sub QuickClear()
Worksheets("NoMatch_Old").Range("A2:R3000").ClearContents
Worksheets("Match_New").Range("A2:V3000").ClearContents
Worksheets("Match_Old").Range("A2:R3000").ClearContents
Worksheets("NoMatch_New").Range("A2:V3000").ClearContents
End Sub
[/vba]