Comparing two columns of phone numbers, counting qty of duplicates

[mrandles83]

So Im trying to compare (as you can tell from the picture) the number of “Leads” that were “called” the problem is the data comes from two sources and is formatted slightly different.

Column A (Leads to be called)
the numbers are formatted as
xxx-xxx-xxxx

Column B (Outbound Calls)
are formatted as
(xxx)-xxx-xxxx

So we are looking for a way to compare -only- the numbers in the two columns, and generate a total quantity of matches (so I can see how many leads were contacted) -preferably a solution that doesn’t involve Find&Replace on all the area codes.

[anon73440385]

Hello,

if you are really interested in the number of matches only, then you may use a formula using following assumption:

LEADS: Range A2:A40
CALLED: Range B2:B20

Formula for number of matches:

=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(B2:B20,"[()]","","g"),A2:A40,1,0)),0))

or using named ranges (which I’d prefer here):

=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(CALLED,"[()]","","g"),LEADS,1,0)),0))

Note(s).

1. I’m pretty sure there will be some guys here having a smarter formula
2. The formula only works, if both columns do not contain any deviation from their respective format
3. Use of SUMPRODUCT is only to force array function to work and to avoid {} using CTRL+SHIFT+ENTER but {=COUNTIF(ISERROR(VLOOKUP(REGEX(CALLED,"[()]","","g"),LEADS,1,0)),0)} will work as well.

Update (OP states different format of column B in comment)

If column B contains a whitespace character after the area code you may use:

=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(REGEX(B2:B20,"[()]","","g"),"\s","-",1),A2:A40,1,0)),0))
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(REGEX(CALLED,"[()]","","g"),"\s","-",1),LEADS,1,0)),0))

Another variant (avoidung nested REGEX)
=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(B2:B20,"[()\s-]","","g"),REGEX(A2:A40,"[()\s-]","","g"),1,0)),0))

[mrandles83]

So I tired the first one, adjusted the Range to match the length of the columns and got “Err: 504”
The second one gave the same Err: 504

The third one, I my have copy/pasted incorrectly into C2
=sumproduct{=COUNTIF(ISERROR(VLOOKUP(regex(‘CALLED’,"[()]","",“g”),‘LEADS’,1
Gave “Err: 533”

[mrandles83]

Okay I think its related to the formatting of the number in column B.
They are a actually:
Column A
xxx-xxx-xxxx

Column B
(xxx) xxx-xxxx (difference being no hyphen, but a space, after the closing parenthesis)

[anon73440385]

Tja - see my Note 2. and see your format description of column B in the question (Before I post such formulas, I assure them working on an example)

Regarding 3:
Did I really write =sumproduct{=COUNTIF(ISERROR(VLOOKUP(regex('CALLED',"[()]","","g"),'LEADS',...-? No. And please leave it off, if you don’t know anything about array functions and don’t understand why I wrote to avoid {} using CTRL+SHIFT+ENTER (Array functions are to be entered without the curly brackets, but finalized with CTRL+SHIFT+ENTER instead of ENTER. This makes the curly backets to appear).

[mrandles83]

Apologies for the error regarding formatting of column B, and thank you for attempting to help so far,

I have done a direct copy/paste trying the 3 new formulas provided, all with, and without adjusting the ranges as needed (except the second one as it specifies the column names), and am still receiving
Err: 504

=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(regex(B2:B50,"[()\s-]","",“g”),regex(A2:A1440,"[()\s-]","",“g”),1,0)),0))

Opening the Function wizard, and clicking on the Structure Tab shows #NAME? after COUNTIF, ISERROR, and VLOOKUP (link to screenshot is below)

[Album] imgur.com

[anon73440385]

Caused by not giving any information of your LibreOffice version in use. REGEX() function is a new function introduced in LibreOffice version 6.2 (See Release Notes 6.2) - Remind: #NAME? most likely is related to an unknown function name or range - See LibreOffice Calc Error Codes

[SM_Riga]

If using LO version before Regex() was introduced:

=SUMPRODUCT(COUNTIF(SUBSTITUTE(MID(B2:B20;2;20);") ";"-");$A$2:$A$40))

[mrandles83]

yep that was the bugger, was on 6.0.6.2, updated to 6.3.xx and it works great with the regex. Thanks for the help!

[gregors15]

Hi,
Try =CONCATENATE(MID(B1,2,3),RIGHT(B1,9)) in column C, and do your comparison between columns A and C.
Let us know if it helps.

[mrandles83]

Tried this,
the cell I pasted into (C2) now shows “ALLCALLED”

[mrandles83]

Updated to latest version and used

=SUMPRODUCT(COUNTIF(ISERROR(VLOOKUP(REGEX(B2:B50,"[()\s-]","",“g”),REGEX(A2:A1540,"[()\s-]","",“g”),1,0)),0))

[anon73440385]

And what additional information does this Answer provide, which is not already given by other answers?