Categorizing phone numbers according to pattern

hasanlianar

New Member
Joined
Jul 21, 2011
Messages
43
Hello,

I have a list of phone numbers and patterns as shown below. Is it possible to somehow match phone numbers with patters. For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA. I only can think of nested IF formulas, though that formula will be too long and complicated.
Could someone help with a better idea? Please note that there can be even more numbers, for example, instead of 2000000, there can be 3000000


phone numberspatterns
2000000AAAAAAA
2000001A000000
2000002A00000A
2000022A00000B
2000033AA00000
2002222AB00000
2003333AAA0000
2020000A0A0000
2020002A0A0A0A
2020202A0AAAAA
2022222ABABABA
2023333ABAAAAA
2030000ABBBBBB
2030303AAAAAA0
2032222AAAAAAB
2033333AA0A0A0
2034444AAB0000
2100000AABBBBB
2200000AABABAB
2200002A0B0000
2200003A0B0B0B
2200022A0BBBBB
2200033AB0B0B0
2202020ABA0000
2202222ABB0000
2203333ABBBBBA
2220000ABC0000
2220002ABCBCBC
2222000ABCCCCC
2222200AAA000A
2222220AAABBBB
2222222AAAA000
2222223AAAABBB
2222233AAAAA00
2222333AAAAABB
2223333A0000AA
2230000A0000BB
2232222A00AAAA
2232323A00BBBB
2233333AA0000A
2234444AA0000B
2300002AA000AA
2300003AA000BB
2300004AA0AAAA
2302222AA0BBBB
2303030AABAAAA
2303333AABCCCC
2304444A0BAAAA
2320000A0BCCCC
2320002A0A000A
2322222A0ABBBB
2323232AB0000A
2323333AB0000B
2324444AB0000C
2330000AB0AAAA
2330003AB0BBBB
2332222AB0CCCC
2333332ABCAAAA
2333333ABCBBBB
2334444ABCDDDD
2340000ABA000A
2342222ABABBBB
2343333ABACCCC
2343434ABB000B
2344444ABBAAAA
2345555ABBCCCC

<tbody>
</tbody>
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,416
So ALL phone numbers are 7 digits in length yes?

Referring to your description of the problem:

And 2342222 matches with ABCAAAA
So the first digit in the numbers gets given the letter A, any new digit gets given the next letter in the alphabet, ie B then C.
Since we already have 2 as the first digit the remaining digits also being 2 are given the existing letter A to result in ABCAAA.
Is that correct?

So we could never have BCDBBB could we? Or CDECCC? Since we ALWAYS since the first digit will always be the letter A.
Is that correct?

Now referring to your list of numbers they seem to follow a different pattern what you've described earlier.

I'm wondering what the point of all this is?
 
Last edited:

hasanlianar

New Member
Joined
Jul 21, 2011
Messages
43
Thanks for the comments.
All phone numbers are 7 digits.
First letter is always A. The rest can vary. The point here is that numbers matching different patters are sold for different prices. For example, A000000 (3000000, 8000000,...) is sold for higher price than A00000A (3000003, 8000008, ... ), since demand for A000000 is higher. I need to find to which patters a given number belongs to, so that I can later find its price.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
hasanlianar, it sounds like a simple VLOOKUP will do what you want.

B11: =VLOOKUP(A11,A2:B8,2,FALSE)

Or am I missing something?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">phone numbers</td><td style="font-weight: bold;text-align: center;color: #333333;;">patterns</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">2000000</td><td style="text-align: center;color: #333333;;">AAAAAAA</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">2000001</td><td style="text-align: center;color: #333333;;">A000000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">2000002</td><td style="text-align: center;color: #333333;;">A00000A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">2000022</td><td style="text-align: center;color: #333333;;">A00000B</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">2000033</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">AA00000</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;color: #333333;;">2002222</td><td style="text-align: center;color: #333333;;">AB00000</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;color: #333333;;">2003333</td><td style="text-align: center;color: #333333;;">AAA0000</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;background-color: #FFFF00;;">Find this one:</td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">2000033</td><td style="text-align: center;background-color: #FFFF00;;">AA00000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748

ADVERTISEMENT

Per original post: "For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA..."

These don't match what I see in the sample data from the original post.

When I look beside 2000000, I see AAAAAAA.

When I look beside 2342222, I see ABABBBB.

???
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,132
Per original post: "For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA..."

These don't match what I see in the sample data from the original post.

When I look beside 2000000, I see AAAAAAA.

When I look beside 2342222, I see ABABBBB.

???

The goal is to match the pattern of column A to the pattern in column B. If A2 = 2000000, then the corresponding pattern in column B would be A000000. Now to match that pattern with A1 wherever it is in column B.
 
Last edited:

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748

ADVERTISEMENT

@dried1011, I understand the goal. But the poster's original data doesn't match later data. If you look at the list he posted as sample data, it starts this way:

phone numberspatterns
2000000AAAAAAA
2000001A000000
2000002A00000A
2000022A00000B


<tbody>
</tbody>

... so 2000000 matches AAAAAAA not A000000 in his original data set. So I'm not seeing the pattern correlation.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
Or am I missing something?

Oops yes, everything! :oops:

With helper cells:

B2: =CHAR(C2)&CHAR(D2)&CHAR(E2)&CHAR(F2)&CHAR(G2)&CHAR(H2)&CHAR(I2)
C2: 65
D2: =IF(MID($A2,COLUMNS($C2:D2),1)="0",48,IFERROR(INDEX($C2:$I2,FIND(MID($A2,COLUMNS($C2:D2),1),LEFT($A2,COLUMNS($C2:C2)))),MAX($C2:C2)+1))

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">Numbers</td><td style="font-weight: bold;text-align: center;color: #333333;;">Pattern</td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">2000000</td><td style="text-align: center;color: #333333;;">A000000</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">2000001</td><td style="text-align: center;color: #333333;;">A00000B</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">66</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">2342222</td><td style="text-align: center;color: #333333;;">ABCAAAA</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">66</td><td style="text-align: right;;">67</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">2340436</td><td style="text-align: center;color: #333333;;">ABC0CBD</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">66</td><td style="text-align: right;;">67</td><td style="text-align: right;;">48</td><td style="text-align: right;;">67</td><td style="text-align: right;;">66</td><td style="text-align: right;;">68</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;;">2030432</td><td style="text-align: center;color: #333333;;">A0B0CBA</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">66</td><td style="text-align: right;;">48</td><td style="text-align: right;;">67</td><td style="text-align: right;;">66</td><td style="text-align: right;;">65</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
Wait, Hasan ... I think I'm finally understanding what you're trying to do.

Could you please decode the following:

2000001

4010009


Would they be ...?:

A000001

C01000H

If not, please correct and explain. Then I'll be able to help you better. (It's all coming together in my mind ...)
 
Last edited:

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
If I'm understanding you correctly, then a formula for "translating" any phone number would be as follows assuming phone number is in A2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H")

This can be used to match any phone number against a list. For instance, if your price-per-pattern is listed in a table, you could use the above with INDEX-MATCH to find the price that corresponds with the pattern, having only entered the phone number.

So let's say that we formatted the phone number list as a table called Table1 in Column A. And we formatted the unsorted patterns list as a table called Table2 in Column B. We could then place the following formula into cell D2:

=IFERROR("Table 2 Row "&MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H"),Table2,0),"---NoMatch---")

... and copy-drag it down as far as we want.

This would tell us where to find each number from Table1 in Table2.

Or, if you just wanted a conversion machine, someone could enter any number in A2 and have B2 create the conversion pattern by placing the first formula above in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H")
 
Last edited:

Forum statistics

Threads
1,141,711
Messages
5,708,004
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top