Need some additional changes in Match Portal code

[RAJESH1960]

Hello guys
This code is already running perfect. I need your expertise to make some changes and add a few more lines of code to update this. I have tried to explain the steps in the conditions sheet as simple as possible.

Additonal Changes.xlsm

 

[johnnyL]

Hey there.

The following should do step #2:

Sub Copy_Rename_MatchedSheet()
'
    Dim FirstMatch                  As Long
    Dim DestinationRemarksColumn    As String
    Dim LastColumn                  As String
'
    DestinationRemarksColumn = "J"                                                                                  ' <--- Set this to the 'Remarks' column letter
    LastColumn = "N"                                                                                                ' <--- Set this to the last column letter used in the sheet
'
    Sheets("Matched").Copy after:=Sheets(Sheets.Count)                                                              ' Copy/add worksheet
    ActiveSheet.Name = "to correct Invoice No. & Date"                                                              ' Rename the new sheet
'
    LastRow = Range("A" & Rows.Count).End(xlUp).Row                                                                 ' Get LastRow used of new sheet
'
    With Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & LastRow)
        .Formula = "=IF(COUNTIFS($C$2:$C$" & LastRow & ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$E$2:$E$" & _
                LastRow & ",$E2)=0,""Invoice No. Mismatch"","""")&IF(COUNTIFS($C$2:$C$" & LastRow & _
                ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$F$2:$F$" & LastRow & ",$F2)=0,""Date Mismatch"","""")" & _
                "&IF(COUNTIFS($C$2:$C$" & LastRow & ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$E$2:$E$" & _
                LastRow & ",$E2,$F$2:$F$" & LastRow & ",$F2)>0,""Matched"","""")"                                   '   Formula to write to the range
        .Copy                                                                                                       '   Copy the formula range
        .PasteSpecial xlPasteValues                                                                                 '   Paste just the values back to range
        Application.CutCopyMode = False                                                                             '   Clear clipboard & 'marching ants' around copied range
    End With
'
    Range(DestinationRemarksColumn & "1").EntireColumn.AutoFit                                                      ' Adjust the width of the DestinationRemarksColumn
'
    Range("A2:" & LastColumn & Range("A" & Rows.Count).End(xlUp).Row).Sort _
            Key1:=Range(DestinationRemarksColumn & "2"), Order1:=xlAscending, Header:=xlNo                          ' Sort Remarks Column lowest to highest
'
    FirstMatch = Columns(DestinationRemarksColumn).Find(what:="Matched", after:=Range(DestinationRemarksColumn & "1")).Row  ' Find first cell in DestinationRemarksColumn that = 'Matched
'
    Rows(FirstMatch & ":" & LastRow).Delete                                                                         ' Delete the 'Matched' rows
End Sub

 

[johnnyL]

The following should do step 1:

        With .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & .Range("A" & .Rows.Count).End(xlUp).Row)   '
            OffsetAmount = AsPerColumnNumber - DestinationRemarksColumnNumber
'
            .Value = Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Offset(, OffsetAmount).Address & _
                    "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Address))
        End With

 

[johnnyL]

Still confused about step #3.

 

[RAJESH1960]

Hey there.

The following should do step #2:

Sub Copy_Rename_MatchedSheet()
'
    Dim FirstMatch                  As Long
    Dim DestinationRemarksColumn    As String
    Dim LastColumn                  As String
'
    DestinationRemarksColumn = "J"                                                                                  ' <--- Set this to the 'Remarks' column letter
    LastColumn = "N"                                                                                                ' <--- Set this to the last column letter used in the sheet
'
    Sheets("Matched").Copy after:=Sheets(Sheets.Count)                                                              ' Copy/add worksheet
    ActiveSheet.Name = "to correct Invoice No. & Date"                                                              ' Rename the new sheet
'
    LastRow = Range("A" & Rows.Count).End(xlUp).Row                                                                 ' Get LastRow used of new sheet
'
    With Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & LastRow)
        .Formula = "=IF(COUNTIFS($C$2:$C$" & LastRow & ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$E$2:$E$" & _
                LastRow & ",$E2)=0,""Invoice No. Mismatch"","""")&IF(COUNTIFS($C$2:$C$" & LastRow & _
                ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$F$2:$F$" & LastRow & ",$F2)=0,""Date Mismatch"","""")" & _
                "&IF(COUNTIFS($C$2:$C$" & LastRow & ",$C2,$B$2:$B$" & LastRow & ",""<>""&$B2,$E$2:$E$" & _
                LastRow & ",$E2,$F$2:$F$" & LastRow & ",$F2)>0,""Matched"","""")"                                   '   Formula to write to the range
        .Copy                                                                                                       '   Copy the formula range
        .PasteSpecial xlPasteValues                                                                                 '   Paste just the values back to range
        Application.CutCopyMode = False                                                                             '   Clear clipboard & 'marching ants' around copied range
    End With
'
    Range(DestinationRemarksColumn & "1").EntireColumn.AutoFit                                                      ' Adjust the width of the DestinationRemarksColumn
'
    Range("A2:" & LastColumn & Range("A" & Rows.Count).End(xlUp).Row).Sort _
            Key1:=Range(DestinationRemarksColumn & "2"), Order1:=xlAscending, Header:=xlNo                          ' Sort Remarks Column lowest to highest
'
    FirstMatch = Columns(DestinationRemarksColumn).Find(what:="Matched", after:=Range(DestinationRemarksColumn & "1")).Row  ' Find first cell in DestinationRemarksColumn that = 'Matched
'
    Rows(FirstMatch & ":" & LastRow).Delete                                                                         ' Delete the 'Matched' rows
End Sub

Step 2 Done.
Dim LastRow As Long
Added this line as I was getting an error.
checking the others now.

 

[RAJESH1960]

The following should do step 1:

        With .Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & .Range("A" & .Rows.Count).End(xlUp).Row)   '
            OffsetAmount = AsPerColumnNumber - DestinationRemarksColumnNumber
'
            .Value = Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Offset(, OffsetAmount).Address & _
                    "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Address))
        End With

Where do I add these lines and also I am getting an error to define as per column number ?

 

[RAJESH1960]

Still confused about step #3.

I have created that temporarily for now. Will let you know once I get to know the final table to reconcile which will be connected to almost all the sheets. I added the step 1 code in the match portal sheet but I am getting an error.

 

[johnnyL]

Where do I add these lines and also I am getting an error to define as per column number ?

That goes in the 'Match Portal Tally' subroutine.

Down at the last formula section ... The formula that ends with ""Not Found""

Place the code right below the line:

'
        .Range("A2:N" & LastRow).Sort Key1:=.Range(DestinationRemarksColumn & "2"), Order1:=xlDescending, Header:=xlNo  '   Sort Remarks Column highest to lowest

As far as the 'As Per' question ...

             AsPerColumnNumber = 2                                                                                      ' <--- Set this to the 'As Per' column number in 'Mismatches'

 

[johnnyL]

You will also have to add:

'
   DestinationRemarksColumnNumber = Range(DestinationRemarksColumn & 1).Column                                          ' Convert DestinationRemarksColumn letter to a number

To handle that variable

 

[RAJESH1960]

and this one OffsetAmount to be defined as ?????

 

[johnnyL]

It is to be dimmed as Long