detect a # in the middle.... 2

[natedeia]

dont know if this would be vb or better as a conditiona b/c i do not know where to start with this one especially.
is it possible to detect the fifth ( or fourth, third, etc) digit in a # (8759 6 5687)it is not seperated like that but i want to be able to search a column and.....well this is part of a bigger solution that i have posted on, so going to take it one step at a time. thanks!

i am in the situation to be forced to learn vb for excel just because management do not want to get off their a#% to get our vb expert in here to create this stuff.

 

[DrSimon]

I'm not really clear what you want. I think you want to consider that string without the spaces. If so, use replace as in:
NewString=Replace(OldString," ","")
Then you could just use Mid() to extract the digit you want from NewString.

If that's not what you mean, let us know!
Simon Rouse

 

[natedeia]

Thanks Doc for replying, I am working on a feature in an Excel spreadsheet that will detect masking. I query a DB for information, then paste into a spreadsheet. I am trying to detect whether or not another # in a particular column is very similar to another. What happens is that theives stealing from our company are using our service and are using stolen idenities or made up ones. They will just change a couple of the digits, it is always random. It can be the first two, or three, or middle two, or last four, etc....Only way we can see it is look with the eye balls, but these are not always grouped together, sometimes scattered through the data and if you sort it will not show all the time cause they do not just change the back end or just the begining.
What we (or actually I, being proactive here) need it to have the cell highlight so we can take a look at it. I need a cell that I could type the # 5 or 3 or 6, etc in to limit the formula or code. It would detect whether the cell is similar to another cell in that column where 5 of the digits match and match in the same place.

12345678
12322678
12399678
12388678
this one matches at least 6 digits and their placements.
I have a conditional that will match if a cell has the same digits but not necessarily in the same placement, which flaggs too much.
But there it is, I was trying to get someone to answer the first thing, then try to work with that with my little knowlege and come up with something hopefully.

 

[DrSimon]

I'm beginning to understand the problem but need some more information. Are all the numbers unique or are any replicated? Are they always the same length? Are the number of digits changed always 2 or can it be more?

Clearly I know nothing about your setup, but a possible way to stop this happening is to change your numbers by using a modulus check character. This where a function is applied to the numbers which calculates another number/character which you add to the end. You can then validate those numbers by using the function again to confirm the check character.

 

[natedeia]

Had to read that second paragraph a couple times to understand that, certainly a different approach, probably many of which I am not aware of yet.
But to put it plain and simple, these are transactions for (something), which the number in question is actually a social security #. They are from all over the US so they all start with different digits. Once I can overcome the problem of figuring this one out I can then apply it to the ID presented as well because we see that too. It is just that I am too busy looking for other stuff that it is easy to pass over a masked # just because at a glance they can look the same, not noticing two or one digit is being changed. Hope that give a better idea, the rest of the info pulled from the DB are just other basic transaction type of info that would not have any bearing on the SS or ID. Thanks for the question.

 

[xlbo]

natedeia - what are you basing the check on ? Do you have a valid number that you want to check against or are you checking all records vs all other records ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

exactly , records against all records. cant be just one #, i have that already and there is not much sense with using that function. however i use that function for many other things in this spreadsheet.
this one here that i am thinking of will be great b/c i can use it in the other fields

 

[xlbo]

So how would you want it flagged if it finds matches ??



Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

any way at all whether it is taking those rows and putting them on another worksheet, or simply highlighting them, make bold, what ever it is would help greatly when analyzing the data. stopping the fraud is what this is all about. thanks for the reply

 

[xlbo]

Yes but how are you wanting to highlight that number X is similar to number Y AND nuber A AND number B (if that can happen) - you need a way of matching the "close matches" to the original number and how you want to show that will be crucial to figuring out the best way to do this....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

well that is not what i imagined, i was thinking more towards highlighting the cell itself, not the individual ones. it would not matter to me if going down through the rows, get to row 306 and the # is highlighted then the next one highlighted is over a hundred rows, then the next is in some other random cell. it is just that if it has 'so many' digits matching that is an indicator for me and i will check out that transaction and others that may have related info or was done at that time, or ....but there could be one hightlighted or flagged on row 100 and the next on row 900 which neither could be related but those two are similar to another or others on the spreadsheet.

 

[xlbo]

Right - so you just want to highlight ones that look similar to other ones ??

Lemme have a think about this one but I have an idea....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

thank you xlbo, any ideas will be better than mine! thanks

 

[Zathras]

Sounded like an interesting challenge. I couldn't resist. See if this is anything like you want:

Option Explicit
Const THEFT_THRESHHOLD = 0.75
Const SEARCH_COLUMN = "A:A"

Sub CheckForPossibleTheft()
Dim r As Range
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nRow As Long
Dim nRowX As Long

  Set r = Intersect(Range(SEARCH_COLUMN), ActiveSheet.UsedRange)
  nFirstRow = 1
  nLastRow = r.Rows.Count[COLOR=green]
  ' Clear all bold[/color]
  For nRow = nFirstRow To nLastRow
    r.Cells(nRow, 1).Font.Bold = False
  Next nRow[COLOR=green]
  ' Bold all near same pairs[/color]
  For nRow = nFirstRow To nLastRow - 1
    For nRowX = nRow + 1 To nLastRow
      If NearSame(r.Cells(nRow, 1), r.Cells(nRowX, 1)) Then
        r.Cells(nRow, 1).Font.Bold = True
        r.Cells(nRowX, 1).Font.Bold = True
      End If
    Next nRowX
  Next nRow
End Sub

Private Function NearSame(A As String, b As String) As Boolean
Dim x As Integer
Dim c As Integer
  If A <> "" Then
    For x = 1 To Len(A)
      If Mid(A, x, 1) = Mid(b, x, 1) Then c = c + 1
    Next x
    If c / Len(A) >= THEFT_THRESHHOLD Then
      NearSame = True
    Else
      NearSame = False
    End If
  End If
End Function

 

[natedeia]

now that is most impressive! i have spoke to a couple people and posted this type of question before and no one could come up with a piece of it. that is why i just asked for that one feature of the code or function on this thread.
got to hand it to ya, works great. i am going to study the hell out of that, pretty complicated.
i going to feel like an A#% but can you insert the function to not flagg the exact ones?

 

[natedeia]

again Zathras, that is why you make the big bucks

 

[xlbo]

Zathras - as usual, you've managed to KISS. I was going to look into looping the FIND function with a ? in each of the positions to find close matches. I may still do that as it should run quicker than a row by row comparison....

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

I didn't test it but you should be able to change one line in the NearSame function to read something like this:
[tt]
If (c <> Len(A)) And (c / Len(A) >= THEFT_THRESHHOLD) Then
[/tt]

 

[natedeia]

Zathras, it works better, shows less with out those exact matches, it will however show mult exact matches but that may be cause they are matching overlapping. works great though. i did not take the credit on this, got some people impressed with that code just due to the fact that it will show us what we are looking for incase it may be over looked.
only thing at this time is that it will not due alot of rows and that is understandable due to how it is looking at the data.
i can do a couple hundred but if i choose to run it on 4000 rows then it has a little trouble. stops responding, my fix is that i can break up to regions or something which puts me in a better place than before. thanks again for your time on that. it is not everyday someone goes completely out of their way for someone.

 

[Zathras]

Hmmm. That's always a problem when there is a loop within a loop. Processing time increases exponentially.

Here is another version that displays the progress on the status line and also allows you to do other work while it runs in the background. I also did a few tweaks to make it go a little faster.

Option Explicit
Const THEFT_THRESHHOLD = 0.75
Const SEARCH_COLUMN = "A:A"

Sub CheckForPossibleTheft()
Dim r As Range
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nRow As Long
Dim nRowX As Long
Dim sAtRow As String
Dim b As Boolean

  Set r = Intersect(Range(SEARCH_COLUMN), ActiveSheet.UsedRange)
  nFirstRow = 1
  nLastRow = r.Rows.Count[COLOR=green]
  ' Clear all bold[/color]
  For nRow = nFirstRow To nLastRow
    r.Cells(nRow, 1).Font.Bold = False
  Next nRow[COLOR=green]
  ' Bold all near same pairs[/color]
  With r
    For nRow = nFirstRow To nLastRow - 1
      Application.StatusBar = "Checking row " & nRow & " of " & nLastRow
      DoEvents
      sAtRow = .Cells(nRow, 1).Text
      For nRowX = nRow + 1 To nLastRow
        If NearSame(sAtRow, .Cells(nRowX, 1).Text) Then
          .Cells(nRow, 1).Font.Bold = True
          .Cells(nRowX, 1).Font.Bold = True
        End If
      Next nRowX
    Next nRow
  End With
  Application.StatusBar = False
End Sub

Private Function NearSame(A As String, b As String) As Boolean
Dim x As Integer
Dim c As Integer
Dim n As Integer
  n = Len(A)
  If n > 0 Then
    For x = 1 To n
      If Mid(A, x, 1) = Mid(b, x, 1) Then c = c + 1
    Next x
    If (c <> n) Then
      If (c / n >= THEFT_THRESHHOLD) Then
        NearSame = True
      End If
    End If
  End If
End Function