Pls Help..Very Very Challenging

[malcom]

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!!!

 

[mikechambers]

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

 

[yee388]

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
	A	B	C	D
1	CODE	ExpectedResult	MacroResult
2	2999901	XAAAAXX	XAAAAXX
3	2999902	XAAAAXX	XAAAAXX
4	2999111	XAAABBB	XAAABBB
5	2999288	XAAAXBB	XAAAXBB
6	2999399	XAAAXAA	XAAAXAA
7	2999229	ABBBAAB	XAAABBA	here,youmaketheleading"2"significant
8	2999292	ABBBABA	XAAABAB	hereaswell
9	2999889	XAAABBA	XAAABBA
10	2999900	XAAAABB	XAAAABB
11	2999911	XAAAABB	XAAAABB
12	2999922	XAAAABB	XAAAABB	hereyoudonotmaketheleading"2"significant
13	2999933	XAAAABB	XAAAABB
14	2993000	XAAXBBB	XAAXBBB
15	2998000	XAAXBBB	XAAXBBB
16	2995999	XAAXAAA	XAAXAAA	herethe"9"sare"A"sallthewaythrough
17	2996999	XAAXAAA	XAAXAAA	andhere
18	2997999	XAAXAAA	XAAXAAA	andhere
19	2998999	XAAXAAA	XAAXAAA	andhere
20	2993377	XAABBCC	XAABBCC
21	2998282	XAABCBC	XAABCBC
22	2997979	XAABCBC	XAABABA	hereyoumakesomeofthe"9"s"B"s
23	2998080	XAABCBC	XAABCBC
24	2993322	XAABBCC	XAABBCC
25	2998883	XAABBBX	XAABBBX
26	2998889	XAABBBX	XAABBBA	hereaswell
Sheet3

 

[yee388]

Here is the macro I used to get those results...

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

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]

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]

@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, 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]

im not yet sure.. but i think this works fine..
except that i changed

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]

Sorry malcom... I am swamped this week. Perhaps next week I'll have some daylight to dig back into this. Good luck!

 

[malcom]

its ok..
thank you so much..
i appreciate it...