Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Find out the same 3-5 symbols in cells and mark cell

I need to find the same 3-5 symbols or signs in 1 or 2 columns (but as one whole string), and mark them as duplicate in another column,
or just mark them with color, or maybe extract that same cells in other column.

example:

A
1 qw34-5-237890

2 zw34-59asdaz7890

3 zw98a-wrtqyy

I want to extract, mark with color or duplicate that cells (A1, A2) because they have the same "w34-5" symbols in one whole string, or "7890" not (A3), because he don't contain 3-4 symbols in 1 string just 2 "zw". Don't care if I have double check's same cell like in A1 and A2.

Thanks, Robin.
0
otpisani
Asked:
otpisani
  • 6
  • 2
1 Solution
 
aikimarkCommented:
This will put some matching indicator text in column B.
Option Explicit


Public Sub Q_28406055()
    Dim rng As Range
    Dim vData As Variant
    Dim lngRow As Long
    Dim lngLoop As Long
    Dim strText As String
    Dim oRE As Object
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Pattern = "(.{3,5}).*?\^.*?(\1)"
    Set rng = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A1").End(xlDown))
    vData = rng.Value
    Application.ScreenUpdating = False
    For lngRow = 1 To UBound(vData) - 1
        For lngLoop = lngRow + 1 To UBound(vData)
            strText = vData(lngRow, 1) & "^" & vData(lngLoop, 1)
            If oRE.test(strText) Then
                rng.Cells(lngRow, 2).Value = "Matched with " & lngLoop
                rng.Cells(lngLoop, 2).Value = "Matched with " & lngRow
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
aikimarkCommented:
@otpisani

There are several different ways this problem could be solved.  My use of the regular expression engine is but one path.  The regexp engine in this environment stops when it finds the first matching substring.  If you needed to find all the matching substrings it will take more processing.
0
 
aikimarkCommented:
@Robin

Are you actively participating in this question thread?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
otpisaniAuthor Commented:
Hello,

Didn't have chance to try your code until now, and must admit it's working like a charm, just change {8,10}, because got to many marked cells in B column. Thanks a lot for your help.

Regards, Robin.
0
 
aikimarkCommented:
so you didn't need to know how many matches? (and what substrings)
0
 
aikimarkCommented:
Note: This could also be changed to only populate the B column in a forward or a backward direction, instead of populating both matching rows.
0
 
aikimarkCommented:
@Robin
it's working like a charm
Then it is time to close this question.
0
 
otpisaniAuthor Commented:
Hello  aikimark,

Many thanks for solving my problem, you saved me lots of time.

Regards, Robin.
0
 
otpisani1Commented:
Hello,

Need your help for solving problem with 3 sheets in excell.

Regards, Robin.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now