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]

I guess by the lack of response, I am not the only one confused by the wish list in the file.

I believe I have step 1 accomplished, but I am at a loss on how to tackle the other 2 steps.

2    Create New Sheet of Matched  in the name of "to correct Invoice No. & Date" > delete all the remarks from the remarks column                                
    Get Invoice No. Mismatch                                
    Get Invoice No. Mismatch & Date Mismatch                                
    Get Date Mismatch                                
    Sort the data by remarks and delete the Matched rows as not required in this sheet.                                
    Use this formula to get the above but after converting it into a code.                                
    =IF(COUNTIFS($C$2:$C$27,$C2,$B$2:$B$27,"<>"&$B2,$E$2:$E$27,$E2)=0,"Invoice No. Mismatch","")&IF(COUNTIFS($C$2:$C$27,$C2,$B$2:$B$27,"<>"&$B2,$F$2:$F$27,$F2)=0,"Date Mismatch","")&IF(COUNTIFS($C$2:$C$27,$C2,$B$2:$B$27,"<>"&$B2,$E$2:$E$27,$E2,$F$2:$F$27,$F2)>0,"Matched","")

You said create new sheet of 'Matched', but then you talk about mismatches? There are no mismatches on the 'Matched' sheet. ???

Same with step 3, please rephrase what you want to do in that step also.

 

[RAJESH1960]

You said create new sheet of 'Matched', but then you talk about mismatches? There are no mismatches on the 'Matched' sheet. ???

Once the code is run, they are created. If you add the code to create new sheet of Matched at the end of the code, it will work I hope.
Or the code can, as it is creating other sheets like combined data, matches and mismatches it, can create copy of Matched, etc., also.

 

[RAJESH1960]

how to tackle the other 2 steps.

I have got all the codes ready to complete all the steps but I am not able to include and combine them within the code as they are in some different sheets individually.
Code for step2

Option Explicit

' code by WideBoyDixon

Public Sub CopyMatchedSheet()

Dim newSheet As Worksheet
Dim lastRow As Long

On Error Resume Next
Set newSheet = Worksheets("to correct Invoice No. & Date")
On Error GoTo 0
If Not (newSheet Is Nothing) Then
    Application.DisplayAlerts = False
    newSheet.Delete
    Application.DisplayAlerts = True
End If

Sheets("Matched").Copy After:=Sheets(Sheets.Count)
Set newSheet = Sheets(Sheets.Count)
newSheet.Name = "to correct Invoice No. & Date"
lastRow = newSheet.Cells(newSheet.Rows.Count, "A").End(xlUp).Row
newSheet.Range("J2:J" & lastRow).Formula = Replace("=IF(COUNTIFS($C$2:$C$@@,$C2,$B$2:$B$@@,""<>""&$B2,$E$2:$E$@@,$E2)=0,""Invoice No. Mismatch"","""")&IF(COUNTIFS($C$2:$C$@@,$C2,$B$2:$B$@@,""<>""&$B2,$F$2:$F$@@,$F2)=0,""Date Mismatch"","""")&IF(COUNTIFS($C$2:$C$@@,$C2,$B$2:$B$@@,""<>""&$B2,$E$2:$E$@@,$E2,$F$2:$F$@@,$F2)>0,""Matched"","""")", "@@", CStr(lastRow))
newSheet.Range("J1").EntireColumn.AutoFit

End Sub

 

[RAJESH1960]

The most difficult part is to insert the lines of the different codes created and add to your code. You will have have to just define the variables and ranges as per your code and insert the lines accordingly.

 

[RAJESH1960]

There are no mismatches on the 'Matched' sheet. ???

The Matched sheet doesn't match the Invoice No. and Date if it is wrong. There may be differences in the same. so, a new sheet is created with a list of the Invoice No and date that need to be corrected.

 

[RAJESH1960]

If possible once the combined data sheet is created, at the end of the code if you add these lines then maybe the Matched and mismatched sheets the remarks column will show the same as in combined data sheet.

Sub ReplaceNotFound()

'solved by JEC

 With Range("A2", Range("J" & Rows.Count).End(xlUp))
   .Columns(10).Value = Evaluate(Replace("if(#=""Not Found"",#&"" in ""&choose((" & .Columns(2).Address & "=""PORTAL"")+1,""Portal"",""Tally""),#)", "#", .Columns(10).Address))
 End With
End Sub

 

[RAJESH1960]

I have created the sub total of combined data sheet with the help of your code for sub total of Matched sheet. I had to edit a few lines. But getting the display in the form of a table is a challenge. For now, I will leave it to that and do the needful manually.

 

[RAJESH1960]

GTG now. Will see you tonight.

 

[RAJESH1960]

Hello JohnnyL.