Edit and correct the code

[RAJESH1960]

JohnnyL,
I have tested more than 50 different data and all the data worked and got the mismatches perfectly. Please run the code in this workbook and check why this data is not shifting the matched and mismatched data to the respective sheets. This is the only data where this application is not working.
I noticed that there are some 0 values in columns G, H and I maybe due to which the issue. After speaking and understanding from an expert, I learnt that there may or may not be 0 value invoices in some cases which are not required to match. Also there are invoices where Supply Attract Reverse Charge and they are mentioned as Yes, in the 2B sheet. These too are not required to match. I have explained briefly in the workbook the changes to be made in the code for the code to work in all kind of data. Please help me to edit and correct the code.
Code to edit.xlsm

 

[RAJESH1960]

@johnnyL. I have tried to explain the requirement to the best of my knowledge in the conditions sheet. If you have any doubts please let me know.

 

[johnnyL]

Still need to see the expected results.

 

[RAJESH1960]

Still need to see the expected results.

Ok. Will share in some time.

 

[RAJESH1960]

The code when run is creating the combined Sheet and combining the data of 2B and Purchases, from which I continued doing it manually. The mismatches and Matched sheet were blank.
This is the expected result workbook.
In the combined sheet I had to use a helper column O, to get the Yes & Y invoices from the 2B sheet, sorted the data by column O and copied the data of each column to the respective columns in Mismatches sheet.
To get the exempted invoices, I used filter in Portal sheet and filtered the data by Rate, selected 0 and copied the data of each column to the respective columns in Mismatches sheet.
Then I sorted the data of Combined sheet by Remarks > GSTIN > IGST>CGST>as per, copied the matches to the matched sheet and mismatches to the mismatch sheet.
It was so easy to imagine and ask you to help me write the code, but practically doing it is tough and explaining how, is tougher.
3. Code to edit with expected result.xlsm

 

[RAJESH1960]

I am thinking of filtering the ‘Portal’ sheet for the ‘Yes’ / ‘Y’ value rows into an array & then write those rows, along with the same for the k:m columns that are all = 0 into an array & write those rows, but as I said, I will need to see what you expect the sheets to look like when finished.

This is what I did but with formulas and sending the matches and mismatches to the respective sheets manually.

 

[RAJESH1960]

In the combined sheet I had to use a helper column O, to get the Yes & Y invoices from the 2B sheet,

For this you can add one more column in the combined sheet in your code, if required.

 

[RAJESH1960]

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, 14) = "As Per Portal"                               ' 'As Per Portal'
        End If

Here in the last you can add one more column 15 = "Reverse Charge"

 

[RAJESH1960]

I got the data by editing the formula but without the heading

            OutputArray(OutputArrayRow, 15) = SourceArray(SourceArrayRow, 8)               ' Reverse Charge

 

[RAJESH1960]

Got the heading too. Replaced all A1:N1 with A1:O1.

 

[RAJESH1960]

I will leave the rest for you to complete. See you tomorrow. Good Night.