We help IT Professionals succeed at work.
Private
Troubleshooting Question

In Excel, match records by phone number

73 Views
Last Modified: 2020-10-30
Hi there. I have two Excel tabs, both containing name, address, phone number and other columns with information. If there is a phone number match between both tabs, then I need to highlight the phone number in Tab 1 with a yellow color.

Any ideas experts?
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
please attach a sample excel file
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you want the cell to be highlighted in yellow if the phone number appears on the other tab, consider using Conditional Formatting with a formula criteria like:
=COUNTIF('Other tab'!C:C, C2)
This formula counts the how often the phone number in cell C2 appears in column C of worksheet Other tab. Conditional formatting then interprets 0 as no match (FALSE) and any other numeric value as TRUE.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
VBA solution

Sub MarkMatch()
  Dim r As Integer
  r = 2
  
  Dim x As Integer
  x = Worksheets(2).UsedRange.Rows.Count
  
  Dim pn As Range
  Set pn = Worksheets(2).Range(Worksheets(2).Cells(2, 3), Worksheets(2).Cells(x, 3))
  Dim cp As Range
  
  Set cp = Worksheets(1).Cells(r, 3)
  Dim w
  Do While cp <> ""
    Set w = pn.Find(cp.Text, LookIn:=xlValues)
    If Not w Is Nothing Then cp.Interior.ColorIndex = 6
    r = r + 1
    Set cp = Worksheets(1).Cells(r, 3)
  Loop
End Sub
29198163.xlsm
Mike WallerDigital Marketer

Author

Commented:
Thanks Byundt. Do you have a sample file I can take a look at?

Also, thanks HainKurt. In your file, I tried to change the phone number in either tab so that additional fields would be highlighted but it didn't work. How do I update those highlighted fields if something changes?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Have a look at the conditional formatting on Sheet2 of the attached file.
CFPhoneNumbers.xlsm
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you have to run the code again...
or, you can add a shape and call the procedure on click event
or, you can go with conditional formatting

=MATCH(C2,Sheet2!C2:C6,0)
29198163.xlsm
Mike WallerDigital Marketer

Author

Commented:
Thanks Norie and HainKurt. If I change the names of those tabs to something like Tab 1 and Tab 2, does the formula need to change?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
The sheet references in the formula I used should automatically change if you change the sheet names.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
in the code solution, you dont need to, it uses sheet 1 and 2
as long as they are 1st and 2nd, no need to do anything else

Mike WallerDigital Marketer

Author

Commented:
Thanks Norie, that worked great. Also, if I wanted to include additional tabs such as Tab 3 to match phone numbers in Tab 1 and use a different color, is that possible with your solution?
Mike WallerDigital Marketer

Author

Commented:
Okay thanks HainKurt, I'll change those tab names and try that on your solution.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Mike

You could add a conditional formatting rule for each of the other sheets.

The formula would be the same except for the  sheet reference.

So for 'Tab 3' you would use this,

=COUNTIF('Tab 3'!$C:$C,$C2)

and apply a suitable format.
Mike WallerDigital Marketer

Author

Commented:
Norie, okay instead of a phone number, I've decided to match against a unique ID within both tabs. However, if I try to create a Conditional Formatting formula in a brand new spreadsheet using your example file, it's not applying correctly so I must be doing something wrong.

In Tab 1, I have the Unique ID in column C and I converting that column using Data > Text to Columns. The top row is a header row and I have a total of 258 records in that tab.

In Tab 2, I have the Unique ID in column C. The top row is a header row and I have a total of 147 records in that tab.

What are the steps to create that Conditional Formatting formula?


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
it is showing all the values in my previous post

Mike WallerDigital Marketer

Author

Commented:
Okay thanks HainKurt, let me give yours a try
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
click C2

conditional formatting, manage rules
new rule
`use a formula`
copy and paste
=MATCH(C2,Sheet2!$C$1:$C$7,0)
click format and set color/shade etc...
applies to: select the region that you want to apply this

29198163.xlsx
Mike WallerDigital Marketer

Author

Commented:
HainKurt, am I clicking on Tab 1 or Tab 2 when selecting C2?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
conditional formatting should be on tab1
any cell in the range
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Mike

Could you upload a workbook with some sample data?
Mike WallerDigital Marketer

Author

Commented:
HainKurt, I'm looking at your example but still not understanding it. Only the numbers in Sheet1 that match up with the numbers in Sheet2 should be in red so not sure why the numbers 3 and 6 in Sheet1 are in red. Can you explain?
Mike WallerDigital Marketer

Author

Commented:
Norie, I used your formula and I'm having the same issue like I'm seeing in the example HainKurt gave me. Here is my sample file:

conditional_formatting_ex1.xlsm

So I may have not explained very well what I need. If a number in Sheet2 matches the same number in Sheet1, then highlight that number in Sheet1. So in the file here, in Sheet1 all of the cells should be highlighted except for 3 and 6 b/c 3 and 6 aren't found in Sheet2.
Mike WallerDigital Marketer

Author

Commented:
Okay I think I see the issue now. The formula was starting in row 2 and there wasn't a header row. I've attached an example and I see now that numbers 3 and 6 are not highlighted.

conditional_formatting_ex2.xlsm

My question for both of you is there any real difference in using COUNT or using MATCH?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
In your sample workbook, the Conditional Formatting was applied to cells C1:C11. The formula was =COUNTIF(Sheet2!$C:C,$C2), but should have been pointing to the top left cell being conditionally formatted. Change it to point to cell C1:

=COUNTIF(Sheet2!$C:$C,$C1)
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you have a large number of rows being conditionally formatted, MATCH will be faster because it stops looking after finding a match and because lookup functions in general have been optimized in Microsoft 365 and Excel 2019.

If you only have a few hundred rows, it doesn't matter whether you use MATCH or COUNTIF. Both are plenty fast.
Mike WallerDigital Marketer

Author

Commented:
Thanks byundt. What if in Sheet2, I had a header in row 1 called Number? would the formula still be: =COUNTIF(Sheet2!$C:$C,$C1) b/c I added that in and works just the same even though the numbers in Sheet2 were pushed down (starting in row 2). Or does using Count formula ignore non-numeric values?
Mike WallerDigital Marketer

Author

Commented:
HainKurt, here is your example using Match but still can't the numbers in Sheet1 to highlight in red with the exception of 3 and 6.

HainKurt_ex1.xlsx

I added a header called Number in Sheet 2, column C, row 1 so how would this formula change to accommodate for that: =MATCH(C2,Sheet2!$C$2:$C$10,0)

Does $C$2:$C$10 mean rows 2 through 10? and what does the comma zero mean at the end of that formula? And why are 2,5,9 in black and the others in Sheet1 in red when the only non-matching numbers between both sheets is 3 and 6?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
"Sheet2!$C$2:$C$10"  means C2 to C10
it works fine
0 means exact match
it applies the formatting to only matching cells

what are you trying to do?
format both sheets?
this is just formatting sheet 1
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
It doesn't matter if you add header labels or even blank rows to Sheet2 because the COUNTIF formula is looking at all the data in its entire column C.

It matters if you add a header label to Sheet1 (with the Conditional Formatting) to the extent that the CF formula must apply as written to the top left cell in the selected cells being formatted. Now if you later insert a row at the top of Sheet1 for header labels, the CF formulas will be adjusted automatically--if they worked before you inserted the row, they will work afterwards, too.

The same reasoning applies to the MATCH formula suggested by HainKurt. There is one difference, however. HainKurt's formula is looking at Sheet2 cells C2:C10. That range may extend  beyond your data in Sheet2 if you contemplate adding more data later. It may also apply to the entirety of column C without much penalty (assuming you use Microsoft 365 or Excel 2019).

The 0 in the MATCH formula requires an exact match for the value in the first parameter.

You will have to change the CF formula in the HainKurt workbook to refer to the top left cell, however. See attached workbook.
=MATCH(C1,Sheet2!$C$2:$C$10,0)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you make it this way
29198163_1.xlsx
Mike WallerDigital Marketer

Author

Commented:
HainKurt, yes that's exactly what I need. However, if I increase the rows in either Sheet1 or Sheet2, how would I adjust those two formulas?

byundt, I'm using Excel 365, version 16.42 on a Mac Bookpro.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mike WallerDigital Marketer

Author

Commented:
HainKurt, thanks for that clarity and for the file. So on this formula --> =MATCH(C1,Sheet2!$C$2:$C$11,0) does $C$2:$C$11 mean rows 2 through 11?
Mike WallerDigital Marketer

Author

Commented:
Also, if I change the name of Sheet2 to something like Tab 2 would the formula look like this? =MATCH(C1,'Tab 2'!$C$2:$C$11,0) does $C$2:$C$11 with single quotes around the name?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
yes, the formula keeps tracks of tab changes
and tab name has spaces like "My Tab" then in the formula, it is wrapped by i single quote...
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you have the conditional formatting working, try changing the name of the worksheet from Sheet2 to Tab 2. You'll find that Excel automatically updates the CF formula for you, and will even add the single quotes if they are needed.

Assuming that C1 is the top left cell in the selection, your CF formula would be:
=MATCH($C1,'Tab 2'!$C$2:$C$11,0)
Note that I added a $ before C1 so you may apply the CF to other columns.

You may also use:
=MATCH($C1,'Tab 2'!$C$:$C,0)
Because you are using Microsoft 365, you get the enormously faster lookup functions (including MATCH). So referencing an entire column on Tab 2 is totally OK.
Mike WallerDigital Marketer

Author

Commented:
byundt or HainKurt, if I use =MATCH($C1,'Tab 2'!$C$:$C,0) for the entire C column in Tab 2 would I still need to insert something in 'applies to' ?

And using your example to apply the entire column, what would it look like for the NOT MATCH using HainKurt's method?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
in my last post, you don't need to worry
it checks all values in C column...
Mike WallerDigital Marketer

Author

Commented:
Thanks HainKurt. In your latest example, do I also need the NOT MATCH formula or not?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
NOT MATCH?

you need this
=ISNA(MATCH(C1,Sheet2!$C:$C,0))

29198163_3.xlsx