Putting condition on matched cell

[natedeia]

=SUMPRODUCT(--(MID(A$1,ROW(INDIRECT("1:"&LEN(A$1))),1)=MID(A2,ROW
(INDIRECT("1:"&LEN(A2))),1)))>=5


this formula will put a condition on the cell if it matches "x" amount of digits. how can i get this to recognize any cell, or at least a few up or down instead of just the one cell. possible??

 

[ETID]

Do you have an example of the data that this formula is factoring?

 

[natedeia]

it is one column with several other colums that make up rows of information, one of the rows a #'s that are around 8 or 9 digits long, i initally wanted to find a way to highlight or something when there was a match within that same column of another an "X" amount of them matching. so they did not all have to match but want to for example 5 or more numbers , they all have to be in same position like,


98765432
98555432
98733432
98711432

so here 5 of these have digits that match,
so anyways i have this code instead, it will look at cell A1 to determine if any # in column A had digits that match but not in any specific order.

 

[ETID]

I'm sorry but I don't understand the problem yet,

are you saying that you want to identify any repeating sequence of numbers throughout the entire worksheet?

 

[natedeia]

yes, it is masking. i have people masking personal information such as drivers liscense or socials, i can see this stuff but it would be very nice to create a way to see this fraud trend.

 

[ETID]

Have you tried entering your folrmula as an array formula?

 

[natedeia]

i have thought of that, but do not know enough yet. as i read up i did notice that is probably what i have to do.

 

[ETID]

It's a little differnet as you hilite the range that you would normally fill first then type/paste the formula then crtl+shift+enter

 

[natedeia]

so any experts willing to take a stab?

 

[ETID]

So ,...I take it that the array did not work for you?

 

[natedeia]

well that is my problem, ok a newbie here to VB , i am better at action scripting in Flash, so that is my problem, i do not know enough to get something like that going, so i have been searching for similar code or a code that can help in this problem. but as i have been reading up on trying to use VB i came across Array, in my mind i know what has to be done so array sounded more like it.

 

[ETID]

Array functions are not vba but sheet formula functions

Look in the worksheet help on array functions.