Formula to match amounts

[RAJESH1960]

Hello formula experts

I have these rows where I have to match Portal with Tally and get the mismatches and matched entries. I am using this formula to get the matched rows but it is not 100% perfect. The cells marked yellow are the cells which are showing as Matched but the correct answer is that one portal and one tally is matched. The other one in tally should show a mismatch or an error. When I tried sub total to check the result, I came to know that the formula was not working 100% accurate.
I need your expertise to replace or edit the formula to get the correct result in the Remarks column.

Thank you in advance.
Loading Google Sheets

EDIT:
I think a countifs formula will solve the problem but I am not so good at countif functions.

 

[RAJESH1960]

Guys, I have added data of 2 more columns but they may or may not match, But, it may be helpful in solving the above problem where the reason for mismatch is, if there are 2 rows in portal with the same amount in 1 row in tally.
Please note: The fields to match are ID and CGST amount. I can match each entry by using the filter option by ID by referring the date and Number also. But, Since the data, at times, has more than 20,000 rows to match and it becomes too tedious and takes a long time to match.
Please refer this new look which has the original data and the expected result data.
Loading Google Sheets
Hope someone will be able to help me with a formula to get the expected result. Best of Luck.

 

[johnnyL]

I have to say I have absolutely no clue what you are trying to match/not match. For 1 example why is row 6 considered a match?

 

[RAJESH1960]

The formula is matching the ID of Portal with Tally and then checking for the same amounts to match. It is taking the first nearest matching amount between Portal & Tally and showing it as Matched. Since, the formula is applied in such a way that, if there is no exact match, then it can accept a match even if their is a difference in the amount of +1 to -1.
If you check the 6th row the ID is matching the ID of Portal with the Id of Tally in the fifth row, which is the nearest & FIRST match. Again, as there are so many amounts which are common, it is matching the next amount in portal with the next row in Tally.
This is going to take forever to get the perfect formula as this query has been around for more than a year on this forum in different posts. I was satisfied, then, with the replies and answers received, but when I use this regularly, I face some or the other problem in this formula. I will try to explain this query again, as simple as possible if you have any doubts.

 

[RAJESH1960]

The problem arises when there are common multiple amounts in both portal and tally with the same ID. Manually, I try to match these entries with the Number and Date columns into consideration but sometimes they are not a match and even blank sometimes. In such cases, I consider them as a mismatch. Finally, all the mismatches are separated in a different sheet and then using sub total at each change in As per I try to find the difference of mismatched amounts between portal and tally. I know it is a bit complicated and challenging, to write the formula in such a way, but you never know when I will come across a perfect formula. It is just a question of time.

 

[RAJESH1960]

sometimes they are not a match and even blank sometimes.

I meant, the Number and Date columns. Sometimes, as the number is too long in Portal, a short or some of the digits are entered in tally.

 

[bebo021999]

Can we say: matching criterias are:
* ID: exact match
* Number: Partial match
for instant: 803 in PORTAL vs "803 / 4-8-2018" in TALLY
* Date: exact match
* SGGT: (+-) 0.2

???

 

[RAJESH1960]

Ok. I have tried to make it as simple as possible. Try this. Let me not make it more complicated than it is. Instead of the formula, editing the code itself will be easier and possible to get the solution, I am sharing the code from which I got the matched sheet.
In the edited Portal sheet, I have colored the portal entries as green. The Tally entries are in white. I have to match the entries that of portal with Tally. The GSTIN number is the only common value in the whole data. In some cases, the Invoice number in Tally may be partly entered or may be even blank. Also, the date may be different in tally and not an exact of Portal in most of the cases. First of all, the code has to match and find the exact match. Exact match is when the GSTIN number, Invoice number, Date and Amount in IGST / CGST are the same. In the Remarks column, it has to display Exact Match.

Secondly, in the remaining blank cells Remarks column, the code has to match the GSTIN number and Invoice number only to get the Invoice number match and display Invoice Number Matched.

Next, in the remaining blank cells Remarks column, the code has to match the GSTIN number and Date only to get the Invoice number match and display Date Matched.

Next, in the remaining blank cells Remarks column, the code has to match the GSTIN number and IGST / CGST only to get the approximate match with a difference of +1 or -1 value display Approximate Value Match.

All the remaining blank cells in the Remarks column should display blank.

Get the cells with mismatches with the headings in a new sheet with sheet name mismatches, and the rest of the cells in a new Matched sheet with the headings.

    Option Explicit

    Dim DestinationLastRow          As Long
    Dim DestinationRemarksColumn    As String
    Dim wsDestination               As Worksheet

Sub Match_Portal()
'
'solved by JohnnyL 30-03-2022
'Updated on 04-04-2022
'Updated on 05-04-2022 11:30
'Updated on 06-04-2022 3:20

    Application.ScreenUpdating = False                                          ' Turn ScreenUpdating off
'
    Dim DestinationSheetExists      As Boolean, MatchedSheetExists      As Boolean, MismatchesSheetExists   As Boolean
    Dim ArrayColumn                 As Long, ArrayRow                   As Long
    Dim ColumnFirstBlankValueRow    As Long
    Dim MatchedRow                  As Long, MismatchesRow              As Long
    Dim OutputArrayRow              As Long, SourceArrayRow             As Long
    Dim SourceDataStartRow          As Long, SourceLastRow              As Long
    Dim Cel                         As Range
    Dim DestinationSheet            As String, MatchedSheet             As String, MismatchesSheet          As String
    Dim SourceSheet                 As String
    Dim HeaderTitle                 As String
    Dim SourceDataLastWantedColumn  As String, SourceDataStartColumn    As String
    Dim DestintionArray             As Variant, OutputArray             As Variant, SourceArray             As Variant
    Dim HeaderTitlesToPaste         As Variant
    Dim MatchedArray                As Variant, MismatchesArray         As Variant
    Dim wsMatched                   As Worksheet, wsMismatches          As Worksheet
    Dim wsSource                    As Worksheet, ws                    As Worksheet
'
    DestinationSheet = "Edited Portal"                                          ' <--- Set this to the name of the sheet to store the shortened Portal data into
         SourceSheet = "PORTAL"                                                 ' <--- Set this to the Portal sheet that you want data from
        MatchedSheet = "Matched"                                                ' <--- Set this to the Matched sheet that you copy matches to
     MismatchesSheet = "Mismatches"                                             ' <--- Set this to the Mismatches sheet that you copy mismatches to
'
      DestinationRemarksColumn = "J"                                            ' <--- Set this to the 'Remarks' column letter
    SourceDataLastWantedColumn = "P"                                            ' <--- Set this to the last column of wanted data on the source sheet
         SourceDataStartColumn = "A"                                            ' <--- Set this to the starting column of wanted data on the source sheet
            SourceDataStartRow = 7                                              ' <--- Set this to the starting row of data on the source sheet
'
        Set wsDestination = Nothing
   On Error Resume Next                                                                    ' Bypass error generated in next line if sheet does not exist
        Set wsDestination = Sheets(DestinationSheet)                                        ' Assign DestinationSheet to wsDestination
        Set wsSource = Sheets(SourceSheet)                                                 ' Assign SourceSheet to wsSource
        Set wsMatched = Sheets(MatchedSheet)                                                ' Assign MatchedSheet to wsMatched
        Set wsMismatches = Sheets(MismatchesSheet)                                             ' Assign MismatchesSheet to wsMismatches
    On Error GoTo 0                                                                         ' Turn Excel error handling back on
'
' Create DestinationSheet if it doesn't exist
    If Not wsDestination Is Nothing Then DestinationSheetExists = True                      ' Check to see if the DestinationSheet exists
    If DestinationSheetExists = False Then                                                  ' If DestinationSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = DestinationSheet                                 '   Create the DestinationSheet after the Source sheet
        Set wsDestination = Sheets(DestinationSheet)                                        '   Assign the DestinationSheet to wsDestination
    End If
'
' Create MatchedSheet if it doesn't exist
    If Not wsMatched Is Nothing Then MatchedSheetExists = True                              ' Check to see if the MatchedSheet exists
    If MatchedSheetExists = False Then                                                      ' If MatchedSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = MatchedSheet                                     '   Create the MatchedSheet after the Source sheet
        Set wsMatched = Sheets(MatchedSheet)                                                '   Assign the MatchedSheet to wsMatched
    End If
'
' Create MismatchesSheet if it doesn't exist
    If Not wsMismatches Is Nothing Then MismatchesSheetExists = True                        ' Check to see if the MismatchesSheet exists
    If MatchedSheetExists = False Then                                                      ' If MismatchesSheet does not exist then ...
        Sheets.add(after:=wsSource).Name = MismatchesSheet                                  '   Create the MismatchesSheet after the Source sheet
        Set wsMismatches = Sheets(MismatchesSheet)                                          '   Assign the MismatchesSheet to wsMismatches
    End If
'
'---------------------------------------------------------------
'
    SourceLastRow = wsSource.Range("A" & Rows.Count).End(xlUp).Row                          ' Get last row used in column A of the source sheeet
'
    SourceArray = wsSource.Range(SourceDataStartColumn & SourceDataStartRow & _
            ":" & SourceDataLastWantedColumn & SourceLastRow)                               ' Load all needed data from source sheet to 2D 1 based SourceArray RC
'
    ReDim OutputArray(1 To UBound(SourceArray, 1), 1 To UBound(SourceArray, 2))             ' Establish # of rows/columns in 2D 1 based OutputArray
    OutputArrayRow = 0                                                                      ' Initialize OutputArrayRow
'
    For SourceArrayRow = 1 To UBound(SourceArray, 1)                                        ' Loop through all rows of SourceArray
        If Right$(Application.Trim(SourceArray(SourceArrayRow, 3)), 6) = "-Total" Then      '   If a total cell is found in the array then ...(3 represents column C)
            OutputArrayRow = OutputArrayRow + 1                                             '       Increment OutputArrayRow
'
            OutputArray(OutputArrayRow, 1) = OutputArrayRow                                 ' Row #
            OutputArray(OutputArrayRow, 2) = "PORTAL"                                       ' 'PORTAL'
'
            OutputArray(OutputArrayRow, 3) = SourceArray(SourceArrayRow, 1)                 ' GSTIN
            OutputArray(OutputArrayRow, 4) = SourceArray(SourceArrayRow, 2)                 ' Name of supplier
            OutputArray(OutputArrayRow, 5) = Replace(SourceArray(SourceArrayRow, 3), _
                    "-Total", "")                                                           ' Invoice #
            OutputArray(OutputArrayRow, 6) = SourceArray(SourceArrayRow, 5)                 ' Invoice Date
'
            OutputArray(OutputArrayRow, 7) = SourceArray(SourceArrayRow, 11)                ' Integrated Tax
            OutputArray(OutputArrayRow, 8) = SourceArray(SourceArrayRow, 12)                ' Central Tax
            OutputArray(OutputArrayRow, 9) = SourceArray(SourceArrayRow, 13)                ' State/UT Tax
'
            OutputArray(OutputArrayRow, 11) = SourceArray(SourceArrayRow, 6)                ' Invoice value
            OutputArray(OutputArrayRow, 12) = SourceArray(SourceArrayRow, 10)               ' Taxable value
            OutputArray(OutputArrayRow, 13) = SourceArray(SourceArrayRow, 16)               ' Filing Date
'
            OutputArray(OutputArrayRow, 15) = "As Per Portal"                               ' 'As Per Portal'
        End If
    Next
'
'---------------------------------------------------------------
'
    wsDestination.UsedRange.Clear                                                                   ' Delete previous contents from destination sheet
    wsMatched.UsedRange.Clear                                                                       ' Delete any previous contents from MatchedSheet
    wsMismatches.UsedRange.Clear                                                                    ' Delete any previous contents from MismatchesSheet
'
    HeaderTitlesToPaste = Array("Line", "As Per", "GSTIN of supplier", _
            "Trade/Legal name of the Supplier", "Invoice number", "Invoice Date", _
            "Integrated Tax", "Central Tax", "State/UT", "Remarks", "Invoice Value", _
            "Taxable Value", "Filing Date", "Narration", "Data from")                               ' Header row to paste to desired sheets
'
' Write header values into the DestinationSheet, MatchedSheet, & MismatchesSheet
    wsDestination.Range("A1:O1").Value = HeaderTitlesToPaste                                        ' Write header row to DestinationSheet
        wsMatched.Range("A1:O1").Value = HeaderTitlesToPaste                                        ' Write header row to MatchedSheet
        wsMismatches.Range("A1:O1").Value = HeaderTitlesToPaste                                     ' Write header row to MismatchesSheet
'
    wsDestination.Columns("F:F").NumberFormat = "@"                                                 ' Set column to text format to prevent excel changing dates
    wsMatched.Columns("F:F").NumberFormat = "@"                                                     ' Set column to text format to prevent excel changing dates
    wsMismatches.Columns("F:F").NumberFormat = "@"                                                  ' Set column to text format to prevent excel changing dates
    wsDestination.Columns("M:M").NumberFormat = "@"                                                 ' Set column to text format to prevent excel changing dates
    wsMatched.Columns("M:M").NumberFormat = "@"                                                     ' Set column to text format to prevent excel changing dates
    wsMismatches.Columns("M:M").NumberFormat = "@"                                                  ' Set column to text format to prevent excel changing dates
'
    wsDestination.Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray  ' Display results to DestinationSheet
'
    DestinationLastRow = wsDestination.Range("A" & wsDestination.Rows.Count).End(xlUp).Row          ' Get last row used in column A of the destination sheeet
'
    wsDestination.Range("N2:N" & DestinationLastRow).Formula = "=$C$1 & "" "" & C2" & _
            " & ""  "" & $D$1 & "" "" & D2 & ""  "" & $E$1 & "" "" & E2" & _
            " & ""  "" & $F$1 & "" "" & TEXT(F2,""dd-mm-yyyy"") & ""  "" & $K$1" & _
            " & "" "" & K2 & ""  "" & $M$1 & "" "" & TEXT(M2,""DD-MM-YYYY"")"                       ' Copy Narration Formula to Column N
'
    wsDestination.Range("O2:O" & DestinationLastRow) = "As Per Portal"                              ' Copy 'As Per Portal' to Column O
'
    wsDestination.Range("G:I", "K:L").NumberFormat = "0.00"                                         ' Set columns to numeric with 2 decimal places
    wsMatched.Range("G:I", "K:L").NumberFormat = "0.00"                                             ' Set columns to numeric with 2 decimal places
    wsMismatches.Range("G:I", "K:L").NumberFormat = "0.00"                                          ' Set columns to numeric with 2 decimal places
'
    wsDestination.Range("N2:N" & DestinationLastRow).Copy                                           ' Copy formula range into memory
    wsDestination.Range("N2:N" & DestinationLastRow).PasteSpecial xlPasteValues                     ' Paste just the vales back to range
    Application.CutCopyMode = False                                                                 ' Clear clipboard & 'marching ants' around copied range
'
    wsDestination.Range("F:F").NumberFormat = "dd-mm-yyyy"                                          ' Format the date the way we want it to appear
    wsDestination.Columns("F:F").TextToColumns Destination:=wsDestination.Range("F1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    wsDestination.Range("M:M").NumberFormat = "dd-mm-yyyy"                                          ' Format the date the way we want it to appear
    wsDestination.Columns("M:M").TextToColumns Destination:=wsDestination.Range("M1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    wsDestination.Range("B2:M" & DestinationLastRow).Interior.Color = RGB(146, 208, 80)             ' Highlight the range green
    wsDestination.Range("B2:M" & DestinationLastRow).Font.Bold = True                               ' Make the range Bold
'
'---------------------------------------------------------------
'
    For Each ws In Worksheets                                                                       ' Loop through all worksheets in the workbook
        Select Case ws.Name
            Case Is = SourceSheet, DestinationSheet, "Conditions", "2B", "Matched", _
                    "Mismatches"                                                                    '       List of sheets to exclude
'               Skip these sheets
            Case Else                                                                               '       All other sheets ...
                Call GetDataFromDataSheet(ws.Name)                                                  '           Pass sheet name to the sub routine
        End Select
    Next                                                                                            ' Loop back
'
'---------------------------------------------------------------
'
    DestinationLastRow = wsDestination.Range("A" & _
            wsDestination.Rows.Count).End(xlUp).Row                                                 ' Get last row used in column A of the destination sheeet
'
    HeaderTitle = "Integrated Tax"                                                                  ' Set the header title we will look for & sort
    Call SortColumnAndApplyFormulas(HeaderTitle)                                                    ' Pass HeaderTitle to the sub routine
'
    HeaderTitle = "Central Tax"                                                                     ' Set the header title we will look for & sort
    Call SortColumnAndApplyFormulas(HeaderTitle)                                                    ' Pass HeaderTitle to the sub routine
'
 MsgBox "Data extracted successfully. Check Mismatched sheet for more Matches."                             '   Display message to user

    wsDestination.UsedRange.EntireColumn.AutoFit                                                    ' Autofit all of the columns on the destination Sheet
'
'---------------------------------------------------------------
'
    DestintionArray = wsDestination.Range("A2:" & SourceDataLastWantedColumn & _
            DestinationLastRow)                                                                     ' Load all needed data from destination sheet to
'                                                                                                   '   2D 1 based DestintionArray RC
    ReDim MatchedArray(1 To UBound(DestintionArray, 1), 1 To UBound(DestintionArray, 2))            ' Set the # of rows and columns for MatchedArray
    ReDim MismatchesArray(1 To UBound(DestintionArray, 1), 1 To UBound(DestintionArray, 2))         ' Set the # of rows and columns for MismatchesArray
'
    MatchedRow = 0                                                                                  ' Initialize MatchedRow
    MismatchesRow = 0                                                                               ' Initialize MismatchesRow
'
   For ArrayRow = 1 To UBound(DestintionArray, 1)                                                   ' Loop through DestintionArray rows
        On Error GoTo ErrorFound
'
        Select Case DestintionArray(ArrayRow, 10)                                                   '   Get value from column J
            Case Is = "Matched"                                                                     '       If value = 'Matched' then ...
                MatchedRow = MatchedRow + 1                                                         '           Increment MatchedRow
'
                For ArrayColumn = 1 To UBound(DestintionArray, 2)                                   '           Loop through DestintionArray columns
                    MatchedArray(MatchedRow, ArrayColumn) = DestintionArray(ArrayRow, ArrayColumn)  '               Save Destination cell to MatchedArray
                Next                                                                                '           Loop back
            Case Else
ErrorFound:
                Resume Continue                                                                     '           Clear error if it exists
Continue:
                On Error GoTo 0                                                                     '           Turn Excel error handling back on
                MismatchesRow = MismatchesRow + 1                                                   '           Increment MismatchesRow
'
                For ArrayColumn = 1 To UBound(DestintionArray, 2)                                   '           Loop through DestintionArray columns
                    MismatchesArray(MismatchesRow, ArrayColumn) = DestintionArray(ArrayRow, ArrayColumn)  '               Save Destination cell to MismatchesArray
                Next                                                                                '           Loop back
        End Select
    Next                                                                                            ' Loop back
'
    wsMatched.Range("A2").Resize(UBound(MatchedArray, 1), UBound(MatchedArray, 2)) = MatchedArray   ' Display results to Matched sheet
'
    wsMatched.Range("F:F").NumberFormat = "dd-mm-yyyy"                                              ' Format the date the way we want it to appear
    wsMatched.Columns("F:F").TextToColumns Destination:=wsMatched.Range("F1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    wsMatched.Range("M:M").NumberFormat = "dd-mm-yyyy"                                              ' Format the date the way we want it to appear
    wsMatched.Columns("M:M").TextToColumns Destination:=wsMatched.Range("M1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    wsMismatches.Range("A2").Resize(UBound(MismatchesArray, 1), UBound(MismatchesArray, 2)) = MismatchesArray   ' Display results to Mismatches sheet
'
    wsMismatches.Range("F:F").NumberFormat = "dd-mm-yyyy"                                           ' Format the date the way we want it to appear
    wsMismatches.Columns("F:F").TextToColumns Destination:=wsMismatches.Range("F1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    wsMismatches.Range("M:M").NumberFormat = "dd-mm-yyyy"                                           ' Format the date the way we want it to appear
    wsMismatches.Columns("M:M").TextToColumns Destination:=wsMismatches.Range("M1"), _
            DataType:=xlDelimited, FieldInfo:=Array(1, 4)                                           ' Convert text to numeric
'
    For Each Cel In wsMatched.Range("B2:B" & wsMatched.Range("B" & Rows.Count).End(xlUp).Row)       ' Loop through all cells in column B on the Matched sheet
        If Cel.Value = "PORTAL" Then                                                                '   If Cell value is 'PORTAL' then ...
            Cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                        '       Color the row
            Cel.EntireRow.Font.Bold = True                                                          '       Bold the row
        End If
    Next                                                                                            ' Loop back
'
    For Each Cel In wsMismatches.Range("B2:B" & wsMismatches.Range("B" & Rows.Count).End(xlUp).Row) ' Loop through all cells in column B on the Mismatches sheet
        If Cel.Value = "PORTAL" Then                                                                '   If Cell value is 'PORTAL' then ...
            Cel.EntireRow.Interior.Color = RGB(146, 208, 80)                                        '       Color the row
            Cel.EntireRow.Font.Bold = True                                                          '       Bold the row
        End If
    Next                                                                                            ' Loop back
'
'   RANGE SORTER ... Most important column to least important column 3,6,2
    wsDestination.Range("A2:O" & wsDestination.Range("B" & Rows.Count).End(xlUp).Row).Sort _
        Key1:=wsDestination.Range("C2"), Order1:=xlAscending, _
        Key2:=wsDestination.Range("F2"), Order1:=xlAscending, _
        Key3:=wsDestination.Range("B2"), Order1:=xlAscending, Header:=xlNo                          ' Sort the destination sheet by various columns
'
'   RANGE SORTER ... Most important column to least important column 3,6,2
    wsMatched.Range("A2:O" & wsMatched.Range("B" & Rows.Count).End(xlUp).Row).Sort _
        Key1:=wsMatched.Range("C2"), Order1:=xlAscending, _
        Key2:=wsMatched.Range("F2"), Order1:=xlAscending, _
        Key3:=wsMatched.Range("B2"), Order1:=xlAscending, Header:=xlNo                              ' Sort the Matched sheet by various columns
'
'   RANGE SORTER ... Most important column to least important column 3,6,2
    wsMismatches.Range("A2:O" & wsMismatches.Range("B" & Rows.Count).End(xlUp).Row).Sort _
        Key1:=wsMismatches.Range("C2"), Order1:=xlAscending, _
        Key2:=wsMismatches.Range("F2"), Order1:=xlAscending, _
        Key3:=wsMismatches.Range("B2"), Order1:=xlAscending, Header:=xlNo                           ' Sort the Mismatches sheet by various columns
'
    wsMatched.UsedRange.EntireColumn.AutoFit                                                        ' Autofit all of the columns on the MatchedSheet
    wsMismatches.UsedRange.EntireColumn.AutoFit                                                     ' Autofit all of the columns on the MismatchesSheet
'
    Application.ScreenUpdating = True                                                               ' Turn ScreenUpdating back on
End Sub


Sub GetDataFromDataSheet(DataWorkSheet As String)
'
    Dim ArrayColumn                 As Long, ArrayRow       As Long
    Dim CorrectedColumn             As Long
    Dim DataLastColumn              As String, DataLastRow  As Long, DestinationStartRow    As Long
    Dim CorrectedDataArray          As Variant
    Dim DataSheetArray              As Variant
'
    DataLastRow = Sheets(DataWorkSheet).Range("B" & _
            Sheets(DataWorkSheet).Rows.Count).End(xlUp).Row                                         ' Get last row of the Data sheet column B
'
    DataLastColumn = Split(Cells(1, (Sheets(DataWorkSheet).Cells.Find("*", _
            , xlFormulas, , xlByColumns, xlPrevious).Column)).Address, "$")(1)                      ' Get last column letter of the Data sheet
'
    Sheets(DataWorkSheet).Range("C2:C" & DataLastRow) = "As Per " & DataWorkSheet                   ' Copy 'AS PER ' & sheet name to Column C of the sheet
'
    DataSheetArray = Sheets(DataWorkSheet).Range("A2:" & _
            DataLastColumn & DataLastRow)                                                           ' Load Data from Data sheet to 2D 1 based DataSheetArray
'
    ReDim CorrectedDataArray(1 To UBound(DataSheetArray, 1), _
            1 To UBound(DataSheetArray, 2) - 2)                                                     ' Set the number of rows & columns for the CorrectedDataArray
'
    CorrectedColumn = 0                                                                             ' Initialize CorrectedColumn
'
    For ArrayRow = 1 To UBound(DataSheetArray, 1)                                                   ' Loop through the rows of DataSheetArray
        For ArrayColumn = 2 To UBound(DataSheetArray, 2)                                            '   Loop through the columns of DataSheetArray
            Select Case ArrayColumn
                Case 3: ' Skip this Column
                Case Else
                    CorrectedColumn = CorrectedColumn + 1                                           '       Increment CorrectedColumn
'
                    CorrectedDataArray(ArrayRow, CorrectedColumn) = _
                            DataSheetArray(ArrayRow, ArrayColumn)                                   '       Save DataSheetArray data into CorrectedDataArray
            End Select
        Next                                                                                        '   Loop back
'
        CorrectedColumn = 0                                                                         '   Reset CorrectedColumn
    Next                                                                                            ' Loop back
'
    DestinationStartRow = DestinationLastRow + 1                                                    ' Save DestinationLastRow + 1 into DestinationStartRow
'
    wsDestination.Range("B" & DestinationStartRow).Resize(UBound(CorrectedDataArray, _
            1), UBound(CorrectedDataArray, 2)) = CorrectedDataArray                                 ' Display Results to destination sheet
'
    DestinationLastRow = wsDestination.Range("B" & _
            wsDestination.Rows.Count).End(xlUp).Row                                                 ' Recalculate last row used in column B of the destination sheeet
'
    wsDestination.Range("O" & DestinationStartRow & ":O" & _
            DestinationLastRow) = DataSheetArray(1, 3)                                              ' Copy 'As Per ????' to Column O
'
    wsDestination.Range("A" & DestinationStartRow & _
            ":A" & DestinationLastRow).Formula = "=Row() - 1"                                       ' Use formula to set row #s
    wsDestination.Range("A" & DestinationStartRow & ":A" & DestinationLastRow).Copy                 ' Copy formula range into memory
    wsDestination.Range("A" & DestinationStartRow & ":A" & _
            DestinationLastRow).PasteSpecial xlPasteValues                                          ' Paste just the vales back to range
    Application.CutCopyMode = False                                                                 ' Clear clipboard & 'marching ants' around copied range
End Sub


Sub SortColumnAndApplyFormulas(HeaderTitle As String)
'
    Dim ColumnFirstZeroValueRow     As Long
    Dim DSCol                       As String       ' DestinationSortColumn
    Dim FormulaReplacementString1   As String
'
    DSCol = Split(Cells(1, wsDestination.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 7
    wsDestination.Range("A2:O" & DestinationLastRow). _
            Sort Key1:=wsDestination.Range(DSCol & "2"), Order1:=xlDescending, Header:=xlNo             ' Sort HeaderTitle Column highest to lowest
'
    ColumnFirstZeroValueRow = wsDestination.Range(DSCol & "1:" & DSCol & wsDestination.Range("A" & _
            Rows.Count).End(xlUp).Row).Find(what:=0, LookAt:=xlWhole, SearchDirection:=xlNext).Row      ' Locate first row in column with a zero value
'
    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 wsDestination.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
'
    wsDestination.Range(DestinationRemarksColumn & "2").AutoFill wsDestination.Range(DestinationRemarksColumn & _
            "2:" & DestinationRemarksColumn & ColumnFirstZeroValueRow - 1)                              ' Drag the formula down till zero value is found
'
    wsDestination.Range(DestinationRemarksColumn & "2:" & DestinationRemarksColumn & _
            ColumnFirstZeroValueRow - 1).Copy                                                           ' Copy formula range into memory
    wsDestination.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
    
'    With Sheets(MatchedSheet).[A1].CurrentRegion
 '   .Sort [B1], 1, Header:=1
  '  .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9), Replace:=True
   ' ActiveSheet.Outline.ShowLevels RowLevels:=2

    'End With

End Sub

 

[RAJESH1960]

The code is 100% working only if the amounts in Integrated, central and state tax are not similar under each GSTIN number.

 

[RAJESH1960]

Can we say: matching criterias are:
* ID: exact match
* Number: Partial match
for instant: 803 in PORTAL vs "803 / 4-8-2018" in TALLY
* Date: exact match
* SGGT: (+-) 0.2

???

You got it all right but for the date. The date may be different and may not tally in some cases.