Compare phone numbers on different workbooks and transfer results to new workbook


I have three workbooks. Workbook 1 has a column of phone numbers that I need to compare to workbook 2 column of phone numbers. Then transfer match and non-match phone numbers to workbook 3. My issue is moving to the next row for each workbooks 2 and 3 and if I should nest the codes then loop back to the start of the code. I am using a userform command button to start the search. I think the issue maybe that the VBA code is not identiying the workbook 2 and 3. 

Please suggect any videos that will help me learn how to use VBA for this process too.

Sub Button4_Click()

Dim Copy_1 As Range, Copy_2 As Range

Dim Workbook 1 As Workbook

For Each Count In Range(Range("A2"), Range("A2").End(xlDown))

For Each Count In Range(Cells(1, 1), Cells(10, 1))

Do Until IsEmpty(ActiveCell)

Workbooks("Workbook 1.xlsx").Activate

Worksheets("Copy 1").Range("A2").Activate

Do Until Worksheets("Copy 1").Range("A:A").Value = ""

NumRows = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count

Set Copy_1 = Range("A:A").Find(what:=Range("A2").Value, LookIn:=xlValues, LookAt:=xlWhole)

Range("A2").Offset(1, 0).Select

    If Not UserForm Is Nothing Then

    Workbooks("Workbook 3").Worksheets("Match").Range("A:A").Value = Workbook_1.Value

On Error Resume Next

Set Answers = Activeworkbooks.Sheets("Match")

    Workbooks("Workbook 3").Worksheets("Match").Range("A2").Offset(1, 0).Select

    ActiveCell.Offset(1, 0).Select    


   Workbooks("Workbook 3").Worksheets("Non-match").Range("A:A").Value = Workbook_1.Value

 Workbooks("Workbook 3").Worksheets("Non-match").Range("A2").Offset(1, 0).Select

    ActiveCell.Offset(1, 0).Select

End If


End Sub



Hi Allison and welcome to the Forum

I'm not sure where to start with your code since it has several errors which will cause failures whan the VBA code is run e.g. it contains named sheets which are not present in the files you uploaded.

I'll give an alternative answer.
John_Ru (rep: 2147) Oct 12, '21 at 5:29 pm
Thank you John

You answered my second question and I was trying to work with XMatch but I have an array not a list of values to match.
Allison999 (rep: 4) Oct 20, '21 at 8:07 am

Firstly, when an Answer is given, you can add comments on it within the Discussion section below it. Secondly, if it did not solve your problem, you don't need to Select that Answer (in fact you can Deselect it).

Thirdly, I gave an alternative answer since your code was a mess (now you know about variables, I hope you could improve it). 

You say you " have an array" but you don't in the VBA sense- your code tries to loop through a range. I'll see if I have some time later to look at this (but I have a problem at least today.).
John_Ru (rep: 2147) Oct 20, '21 at 8:25 am
Add to Discussion


Selected Answer


Since you have Excel 365, you can use the XMatch (array) function to compare the workbooks (with those file versions). The Microsoft guidance on this is here: XMATCH function and says it "searches for a specified item in an array or range of cells, and then returns the item's relative position" (which is a number, if found).

Using ISNUMBER and IFERROR allows us to show just the matches between workbooks or unique values in each. Each formula looks .complicated (after ranges in sheets have been picked) however.

To use sthis solution, first open both Workbook 1 and Workbook 2 then the attached revised Workbook 3...

In the revision to workbook 3, sheet "Compare" has 3 formulas in row 2 (which spill down to row 101), as follows:

A2: To show (formatted) phone numbers which are "In both workbooks", the formula is:

=IFERROR(INDEX('[Workbook 1.xlsx]Sheet1 (2)'!$A$2:$A$101,XMATCH('[Workbook 2.xlsx]Sheet1'!$A$2:$A$101,'[Workbook 1.xlsx]Sheet1 (2)'!$A$2:$A$101,0,1)),)

B2: To show (formatted) phone numbers which are "In Workbook 1  only":

=IF(ISNUMBER(XMATCH('[Workbook 2.xlsx]Sheet1'!$A$2:$A$101,'[Workbook 1.xlsx]Sheet1 (2)'!$A$2:$A$101,0,1)),"",'[Workbook 1.xlsx]Sheet1 (2)'!$A$2:$A$101)

C2: To show (formatted) phone numbers which are "In Workbook 2  only":

=IF(ISNUMBER(XMATCH('[Workbook 1.xlsx]Sheet1 (2)'!$A$2:$A$101,'[Workbook 2.xlsx]Sheet1'!$A$2:$A$101,0,1)),"",'[Workbook 2.xlsx]Sheet1'!$A$2:$A$101)

(which swaps the Lookup value, and array cf.f. the formula in B2)

To test it, change a number (beyond the first ten rows) in workbook 1 or 2 and you'll see the numbers move in the columns of Workbook 3.

You could put each formula in a separate sheet of Workbook 3 and use Autofilter to filter out blanks or use the FILTER function to produce only filtered results from the cells in Compare. In the second sheet ("Wilth FILTER"), you'll see the formulas:






which only "spill" for however many values the filter produces.

Hope this helps.


Answer the Question

You must create an account to use the forum. Create an Account or Login