Remove symbols from numbers

[light]

Please open the workbook for reference. Looking for a macro that will look at sheet 1 Col G. Am only concerned with the far right number in that column’s cells. Some are plain, some have asterisks, some have parenthesis around them. Need to ignore all those items and just look at the number and pair it to the name in same row in col D. Also do not want the number that appears before the name in Col D. I want the names ranked according to the value of Col G far right. The names are usually in chronological order to Col G’s far right value. But there are ties. Output should look like Sheet2.

The other data I am interested in is in Col M. I want to focus the first number in those cells in that column. Those are not in chronological order but I want the names ranked with their value as seen on sheet 2. They never have extraneous symbols attached to them as the previous section I mentioned. Length of names columns varies from 4 to 30.

Sorry for the title

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Rick Rothstein]

The names are usually in chronological order to Col G’s far right value.

For the first part of your request... your use of the word "usually" is troublesome. Please clarify... are the names always in the correct order as listed or would we have to sort them the make sure they are in the correct order?

[light]

I said "usually" because there are ties as well.

[jindon]

Sub test()
    Dim a, i As Long
    Application.ScreenUpdating = False
    With Sheets("sheet1")
        With .Range("d6", .Range("d" & Rows.Count).End(xlUp)).Resize(, 10)
           a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{1,4,1,1,1,10}])
        End With
    End With
    With CreateObject("VBScript.RegExp")
        For i = 1 To UBound(a, 1)
            a(i, 3) = Empty: a(i, 4) = Empty
            .Pattern = "^\d+ (.+?) *\d.*"
            If .test(a(i, 1)) Then
                a(i, 1) = .Replace(a(i, 1), "$1")
                a(i, 5) = a(i, 1)
            End If
            .Pattern = "\b\d+(?=\D*$)"
            If .test(a(i, 2)) Then a(i, 2) = Val(.Execute(a(i, 2))(0))
            .Pattern = "^\d+\b"
            If .test(a(i, 6)) Then a(i, 6) = Val(.Execute(a(i, 6))(0))
        Next
    End With
    With Sheets("sheet2").[d6].Resize(UBound(a, 1), UBound(a, 2))
        .Resize(, .Columns.Count + 1).EntireColumn.Clear
        .Value = a
        With .Columns(3)
            .Formula = "=rank(" & .Cells(1, 0).Address(0, 0) & "," & .Columns(0).Address & ",0)"
            .Value = .Value
            .Cut .Columns(0)
        End With
        With .Columns("e:f")
            .Sort .Cells(1, 2), 2
            .Columns(3).Formula = "=sumproduct((" & .Cells(1, 2).Address(0, 0) & "<=" & .Columns(2).Address & _
                ")/countif(" & .Columns(2).Address & "," & .Columns(2).Address & "))"
            .Offset(, 1).Font.Bold = True
        End With
    End With
    Application.ScreenUpdating = True
End Sub

[Rick Rothstein]

Here is another macro that you can consider...

Sub RankNames()
  Dim LastRow As Long, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Sheets("Sheet1")
  Set WS2 = Sheets("Sheet2")
  LastRow = WS1.Cells(Rows.Count, "D").End(xlUp).Row
  WS2.Range("D6:J" & LastRow).ClearContents
  With WS2.Range("D6:D" & LastRow)
    .Value = Evaluate(Replace( Replace("IF({1},MID(LEFT('@'!#,FIND(""  "",'@'!#)-1),FIND("" "",'@'!#)+1,99))", "@", WS1.Name), "#", .Address))
    .Copy .Offset(, 4)
    .Offset(, 1).Value = Evaluate("IF({1},SUBSTITUTE(SUBSTITUTE('" & WS1.Name & "'!" & .Offset(, 3).Address & ","")"",""""),""*"",""""))")
    .Offset(, 1).Replace "* ", "", xlPart, , , , False, False
    .Offset(, 1).Value = Evaluate(Replace( Replace("IF({1},RANK('@'!#,'@'!#))", "@", WS2.Name), "#", .Offset(, 1).Address))
    .Offset(, 5).Value = Evaluate(Replace( Replace("IF({1},LEFT('@'!#,FIND("" "",'@'!#)-1))", "@", WS1.Name), "#", .Offset(, 9).Address))
    .Offset(, 4).Resize(, 2).Sort WS2.Columns("I"), xlDescending
    .Offset(, 6).Formula = "=IF(I5<>I6,J5+1,J5)"
    .Offset(, 6).Value = .Offset(, 6).Value
  End With
End Sub

[light]

Wow! I think it would have taken me another lifetime to have figured it out. Thanks jindon and Rick.