Excel formula help

I have a excel sheet with 2 columns

Contact Status  Max Probability
80-Opp          Closed Won
0-NC              Closed Won
40-Pending       30-Connect
10-Working      20- Engagement
80-Opportunity  30-Connect
40-Pending        10- Engagement

I need to check if the no in contact status ie the numeric value before '-' is less than numeric value in max probabilty before '-', then i need the whole value of max probability update the contact status field

For eg.

 10-Working     20- Engagement

10-Working should be replaced by 20- Engagement

OR

We can have 20- E开发者_高级运维ngagement in a new column if the above mentioned conditioned is satisfied, if replacing the contact Status column is difficult How can achieve this?

This little macro will do the trick. Just change Row to the starting row and ColA/B to the two columns you want to use.

Note that GetNum returns -1 if there is no number at the start which, in this implementation, means no copying. It was unclear what you wanted to do tith Closed Won so I chose the safest option. If you do want it copied, just return a huge number instead of -1.

Option Explicit

Function GetNum(s As String) As Integer
    If Mid(s, 1, 1) < "0" Or Mid(s, 1, 1) > "9" Then
        GetNum = -1
    Else
        GetNum = Val(s)
    End If
End Function

Sub Macro1()
    Dim Row As String
    Dim ColA As String
    Dim ColB As String

    ColA = "A"
    ColB = "B"
    Row = "2"
    While Range(ColA & Row).Value <> ""
        If GetNum(Range(ColA & Row).Value) < GetNum(Range(ColB & Row).Value) Then
            Range(ColA & Row).Value = Range(ColB & Row).Value
        End If
        Row = CStr(Val(Row) + 1)
    Wend
End Sub

This was tested on:

to generate:

This macro will do:

Sub a()
ColumnContSt=1
ColumnMaxProb=2
i = 1
While (Cells(i, 2) <> "")
    colB = Cells(i, ColumnMaxProb)
    colA = Cells(i, ColumnContSt)
    posB = InStr(1, colB, "-")
    posA = InStr(1, colA, "-")
    If (posB <> 0 And posA <> 0) Then
        intColB = CInt(Mid(colB, 1, posB - 1))
        intColA = CInt(Mid(colA, 1, posA - 1))

        If (intColA < intColB) Then
            Cells(i, 1) = Cells(i, 2)
        End If
    End If
    i = i + 1
Wend
End Sub

Just change ColumnContSt = 1 and ColumnMaxProb = 2 for your actual column NUMBERS ie. A=1, B=2, etc.

HTH!

If you are flexible about the actual data, the easiest way to do this is to make two columns (can be hidden in some far away sheet):

0   NC
10  Engagement
30  Connect
...

And then use VLOOKUP(CELL_WITH_PROBABILITY, REGION_WITH_TABLE, 2, true). So if you put my small 3 element table in the upper corner of a sheet and probability in C1, it'd look like VLOOKUP(A1:B3, C1, 2, true)