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"))))

 

[RAJESH1960]

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

Edited Portal sheet cell J2

 

[johnnyL]

Can you upload that workbook?

 

[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

I did that already. Do You want the one I replaced the formula and tried..???

 

[johnnyL]

I want to see a workbook that shows the formulas and the matches from those formulas.

 

[johnnyL]

Nevermind, I see the problem. It is an array formula.

 

[johnnyL]

Here ya go:

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")
            .FormulaArray = "=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

 

[johnnyL]

Nevermind, I see the problem. It is an array formula.

I forgot, newer versions of excel don't always show that.

 

[RAJESH1960]

Loading Google Sheets

 

[johnnyL]

Did you try it?

 

[RAJESH1960]

It is working now. I tried deleting 2 rows in Journal and tried that too. Both are working fine. Thanks buddy. I was afraid whether it will take such a big formula or not. But all is well. It is solved now.