Pls Help..Very Very Challenging

malcom

Active Member
Joined
May 2, 2005
Messages
483
i need a formula or a macro that will replace vanity numbers into pattern...
numbers which are quite meaningless will be replaced by "X"... and numbers which form pattern/s will be replaced by "A", "B", "C"....
refer to samples below....

2999901 = XAAAAXX
2999902 = XAAAAXX
2999111 = XAAABBB
2999222 = XAAABBB or ABBBAAA
2991111 = XAABBBB
2993333 = XAABBBB
2993888 = XAAXBBB
2994888 = XAAXBBB
2999499 = XAAAXAA
2999599 = XAAAXAA
2999299 = XAAAXAA
2999288 = XAAAXBB or ABBBACC
2999399 = XAAAXAA
2999229 = ABBBAAB
2999292 = ABBBABA
2999889 = XAAABBA
2999494 = XAAABAB
2992999 = XAAXAAA
2999200 = XAAAXBB
2999199 = XAAAXAA
2997997 = XAABAAB
2999946 = XAAAAXX
2999947 = XAAAAXX
2999948 = XAAAAXX
2999954 = XAAAAXX
2999956 = XAAAAXX
2999983 = XAAAAXX
2999984 = XAAAAXX
2999985 = XAAAAXX
2999986 = XAAAAXX
2999987 = XAAAAXX
2999959 = XAAAAXA
2999969 = XAAAAXA
2999989 = XAAAAXA
2999900 = XAAAABB
2999911 = XAAAABB
2999922 = XAAAABB or ABBBBAA
2999933 = XAAAABB
2993000 = XAAXBBB
2994000 = XAAXBBB
2995000 = XAAXBBB
2996000 = XAAXBBB
2998000 = XAAXBBB
2995999 = XAAXAAA
2996999 = XAAXAAA
2997999 = XAAXAAA
2998999 = XAAXAAA
2993377 = XAABBCC
2993388 = XAABBCC
2993399 = XAABBAA
2998800 = XAABBCC
2992299 = XAABBAA
2997799 = XAABBAA
2998899 = XAABBAA
2992424 = XAABCBC
2998282 = XAABCBC
2997979 = XAABCBC
2998080 = XAABCBC
2993322 = XAABBCC
2995599 = XAABBAA
2998880 = XAABBBX
2998881 = XAABBBX
2998882 = XAABBBX
2998883 = XAABBBX
2998889 = XAABBBX

aside from the pattern shown...
another 1 is....
if at least three zeros or eights are consecutive, they shouldn't be replaced by the letters but the numbers "0" and "8" themselves...

e.g.
2999888 = XAAA888
2998888 = XAA8888 or XXX8888
2340000 = XXX0000
3155000 = XXXX000
2488888 = XX88888
8888459 = 8888XXX
1200001 = XX0000X

thank you very much!!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Ok.. I have a macro that works on 90% of your examples at the top, but there are a few "rule" issues that I can't resolve with the information you've provided. Here's a sampling of your example next to my results, with the questionable portions in red.
Book1
ABCD
1CODEExpectedResultMacroResult
22999901XAAAAXXXAAAAXX
32999902XAAAAXXXAAAAXX
42999111XAAABBBXAAABBB
52999288XAAAXBBXAAAXBB
62999399XAAAXAAXAAAXAA
72999229ABBBAABXAAABBAhere,youmaketheleading"2"significant
82999292ABBBABAXAAABABhereaswell
92999889XAAABBAXAAABBA
102999900XAAAABBXAAAABB
112999911XAAAABBXAAAABB
122999922XAAAABBXAAAABBhereyoudonotmaketheleading"2"significant
132999933XAAAABBXAAAABB
142993000XAAXBBBXAAXBBB
152998000XAAXBBBXAAXBBB
162995999XAAXAAAXAAXAAAherethe"9"sare"A"sallthewaythrough
172996999XAAXAAAXAAXAAAandhere
182997999XAAXAAAXAAXAAAandhere
192998999XAAXAAAXAAXAAAandhere
202993377XAABBCCXAABBCC
212998282XAABCBCXAABCBC
222997979XAABCBCXAABABAhereyoumakesomeofthe"9"s"B"s
232998080XAABCBCXAABCBC
242993322XAABBCCXAABBCC
252998883XAABBBXXAABBBX
262998889XAABBBXXAABBBAhereaswell
Sheet3
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Here is the macro I used to get those results...

NOTE: I haven't added the logic for the 000 or 888 rule yet.

Code:
Sub Coder2()

Range("C2", "C65536").ClearContents

For Each Cell In Range("A2", Range("A65536").End(xlUp))

MyString = "X" & Mid(Cell, 2, Len(Cell - 1))
x = 0

    'Removes all 1-time characters, replaces with "X"
    For i = 1 To Len(Cell)
        
        If Len(WorksheetFunction.Substitute(MyString, Mid(MyString, i, 1), "")) + 1 = Len(Cell) Then
            MyString = WorksheetFunction.Substitute(MyString, Mid(MyString, i, 1), "X")
        End If
    Next i
    
    For i = 1 To Len(MyString)
        If Asc(Mid(MyString, i, 1)) < 65 Then
            x = x + 1
            MyString = WorksheetFunction.Substitute(MyString, Mid(MyString, i, 1), Chr(64 + x))
        End If
    Next i
    

        
    Range("C65536").End(xlUp).Offset(1, 0) = MyString
    
Next Cell

End Sub
 

malcom

Active Member
Joined
May 2, 2005
Messages
483

ADVERTISEMENT

Need more info here. Do 2's = X or A, do 9's always = A. Need more rules.

any number not forming a good pattern will be "X"....
any number forming a good pattern will be "A" "B" or "C"

9 isnt necessarily "A"...
if we have 3666680, this becomes XAAAAXX... since four 6's make a pattern, the letter "A" will be used.. for the second number forming a pattern. "B" will be used.. like: 3444555 = XAAABBB....

8 and 0 are special numbers.. but when they come in less than 3, they're not meaningful..
2388666 = XXXXAAA
3888092 = X888XXX
2882889 = ABBABBX
5558880 = AAA888X
3224466 = XAABBCC
2367777 = XXXAAAA
5468888 = XXX8888
3000444 = X000AAA

notice than when the special "8" or "0" appears in 3 or more(consecutive), the number "8" or "0" will be used.. rather than "A" "B" "C"....

to all: i appreciate for all the help.. :)
 

malcom

Active Member
Joined
May 2, 2005
Messages
483
@yee388
thank you very much!
regarding the "2", its my fault.. cause i just dragged down row10 to 13..
uhmmm... regarding this, 2999922 would be better if considered as ABBBBAA.. but 2999933 = XAAAAXX.. because 2 3's doesnt look special..
this rule is easier to explain coz we read by individual number...
when its by series... like 1234567 is a special number forming a pattern.. but we cant asign ABCDEFG... the objective of this is to segregate vanity numbers.. similar numbers will later be combined.. because similar pattern are of the same price.. numbers which are very special will hav higher price...
some vanity numbers will be
7323456 <-- Series5
1234593 <-- Series5
9456789 <-- Series6
2345678 <-- Series7

sometimes, a number appears only twice but forms a pattern
2446688 <-- XAABBCC
2672671 <-- ABCABCX
3334545 <-- AAABCBC
2637979 <-- XXXABAB

sometimes, a number appears thrice but isnt good to be considerd in a pattern..
3444393 = ABBBAXA? very confusing.. id rather consider this as XBBBXXX.. but if theres a level of sensitivity, the better..:) like.. zero sensitivity will consider it as XBBBXXX... higher sensitivity will consider it as ABBBAXA...

in short, when a number appears once, its always X.. when it appears twice, it depends.. if it forms the pattern AABBCC or ABABAB or ABCABC or AABAAB.. then thats it.. if its 2333362 the "3" forms a pattern for sure.. but 2, using sensitivity zero, the 2 is not recognized.. using sensitivity 1, it will be considered..

am i making this difficult? hope not..

there are so many vanity numbers that i myself still dont know how shall i separate them...any advice is welcome.. :)
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374

ADVERTISEMENT

Well, I'm not saying this is impossible, but it's surely reached the end of my abilities...

My macro's logic is dependent on a simple count of each number in the code - it will require a much more complicated algorithm to check for your ABCABC patterns and distinguish them from other repeating, but non-pattern instances.

I would think that since your requirements are so detailed and that you haven't worked them all out yet, the first thing to do is to create the parameters for this process by creating a comprehensive list of rules, which is the minimum requirement for excel to be able to do this. Without it, you'll have to rely on human manipulation, which can even have subjective variations without a proper set of rules.

It's an interesting project, though, so if you can come up with a list of rules, I'll be we can help you translate them to Excel.
 

malcom

Active Member
Joined
May 2, 2005
Messages
483
im not yet sure.. but i think this works fine..
except that i changed
Code:
MyString = "X" & Mid(Cell, 2, Len(Cell - 1))
to
MyString = cell

because 4447778 should be AAABBBX rather than XAABBBX..
first number not always X.. it jsut so happend on my samples that all X.. my bad.. i just copied few samples...

next..
please make the "8" and "0"....
thank you very much..
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Sorry malcom... I am swamped this week. Perhaps next week I'll have some daylight to dig back into this. Good luck!
 

Forum statistics

Threads
1,141,018
Messages
5,703,757
Members
421,314
Latest member
Mooncake1

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