Replace old formula with new one in the code.

[RAJESH1960]

Hello guys,
I need to replace the old formula in the code with this new formula. I need your expert help to guide me and tell me which lines to comment and which line I should enter the new formula. The old formula is in the below wrap code.

        FormulaReplacementString1 = "MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(" & DSCol & "2-$" & DSCol & "$2:$" & _
                DSCol & "$20000)<=1)*($B$2:$B$20000=""PORTAL""),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(" & _
                DSCol & "2-$" & DSCol & "$2:$" & DSCol & "$20000)<=1)*($B$2:$B$20000=""TALLY""),1,0)))" ' Additional string to insert into formula
'
        With .Range(DestinationRemarksColumn & "2")
            .FormulaArray = "=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                    "$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""""),xxxxxx)" & _
                    ",""Matched"",NA()),NA())"                                                          '   Formula to insert into 'Remarks' column
            .Replace "xxxxxx", FormulaReplacementString1, xlPart                                        '   Insert additional string into formula
        End With
'

The formula to be replaced is

=  IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  =  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200) )   >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),"Matched", "Not Found"))))

 

[johnnyL]

I don't have your data to test this on, but I think it is correct:

Sub SortColumnAndApplyFormulas(HeaderTitle As String)
'
    Dim ColumnFirstZeroValueRow     As Long
    Dim LastRow                     As Long
    Dim DSCol                       As String       ' DestinationSortColumn
    Dim IfReplacementString1        As String
    Dim IfReplacementString2        As String
    Dim SecondIfReplacementString1  As String
    Dim MultiplyReplacementString1  As String
    Dim MultiplyReplacementString2  As String
    Dim MultiplyReplacementString3  As String
    Dim MultiplyReplacementString4  As String
    Dim MultiplyReplacementString5  As String
'
    With wsDestination
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        DSCol = Split(Cells(1, .Range("1:1").Find(HeaderTitle).Column).Address, "$")(1)                 ' Find Column letter of the HeaderTitle we are looking for
'
'       RANGE SORTER ... Least important column to most important column
        .Range("A2:O" & DestinationLastRow).Sort Key1:=.Range(DSCol & "2"), _
                Order1:=xlDescending, Header:=xlNo                                                      ' Sort HeaderTitle Column highest to lowest
'
        ColumnFirstZeroValueRow = .Range(DSCol & "1:" & DSCol & .Range("A" & _
                Rows.Count).End(xlUp).Row).Find(what:=0, LookAt:=xlWhole, SearchDirection:=xlNext).Row  ' Locate first row in column with a zero value
'
' Replacement strings to insert into formula
        SecondIfReplacementString1 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99993)>SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & _
                DSCol & "$" & LastRow & ")<=1)*99991*99992), B9999)"
        IfReplacementString1 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "2)<=1)*99994*99995)<= SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99993), ""Matched"", ""Not Found"")"
        IfReplacementString2 = "IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "2)<=1)*99994*99995)<= SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                "$" & LastRow & ")<=1)*99991*99992),""Matched"", ""Not Found"")"
        MultiplyReplacementString1 = "(C2=$C$2:$C$" & LastRow & ")"
        MultiplyReplacementString2 = "(""Portal""=$B$2:$B$" & LastRow & ")"
        MultiplyReplacementString3 = "(""Tally""=$B$2:$B$" & LastRow & ")"
        MultiplyReplacementString4 = "(C2=$C$2:$C2)"
        MultiplyReplacementString5 = "(B2=$B$2:$B2)"
'
        With .Range(DestinationRemarksColumn & "2")
            .Formula = "=IF(SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                    "$" & LastRow & ")<=1)*99991*99992)=SUM((ABS(" & DSCol & "2-$" & DSCol & "$2:$" & DSCol & _
                    "$" & LastRow & ")<=1)*99991*99993), ""Matched"", IF(SUM((ABS(" & DSCol & "2-$" & DSCol & _
                    "$2:$" & DSCol & "$" & LastRow & ")<=1)*99991*99992)>SUM((ABS(" & DSCol & "2-$" & DSCol & _
                    "$2:$" & DSCol & "$" & LastRow & ")<=1)*99991*99993), A9999, C9999))"               '   Formula to insert into 'Remarks' column
'
' Variables to replace, string used to replace the variable
            .Replace "C9999", SecondIfReplacementString1, xlPart
            .Replace "A9999", IfReplacementString1, xlPart
            .Replace "B9999", IfReplacementString2, xlPart
            .Replace "99991", MultiplyReplacementString1, xlPart
            .Replace "99992", MultiplyReplacementString2, xlPart
            .Replace "99993", MultiplyReplacementString3, xlPart
            .Replace "99994", MultiplyReplacementString4, xlPart
            .Replace "99995", MultiplyReplacementString5, xlPart
        End With
'
        .Range(DestinationRemarksColumn & "2").AutoFill .Range(DestinationRemarksColumn & _
                "2:" & DestinationRemarksColumn & ColumnFirstZeroValueRow - 1)                          ' Drag the formula down till zero value is found
'
        .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & _
                ColumnFirstZeroValueRow - 1).Copy                                                       ' Copy formula range into memory
        .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & _
                ColumnFirstZeroValueRow - 1).PasteSpecial xlPasteValues                                 ' Paste just the vales back to range
        Application.CutCopyMode = False                                                                 ' Clear clipboard & 'marching ants' around copied range
    End With
End Sub

 

[RAJESH1960]

JohnnyL. This is the last one which you shared in the link. I replaced the lines but I am getting an error. Maybe I didn't replace the right lines. Later I deleted the replaced lines and brought it to its original working form with the old formula.
Continue Match Portal.xlsm

 

[johnnyL]

What error and what line?

 

[RAJESH1960]

Wait. I will replace it again and check what error and let you know.

 

[RAJESH1960]

Rectified the error. Now the code runs but the Matched and Mismatches sheet are empty.

 

[johnnyL]

What do you get when you just put your formula in and copy it down?

 

[RAJESH1960]

I get the maximum number of Matches possible. This is when I enter the formula in the edited formula sheet manually.

 

[RAJESH1960]

All other rows show as not found.

 

[johnnyL]

What sheet/Address are you putting the formula into?
What is the formula you are putting in?