Need expert help to add / edit Code

[RAJESH1960]

Hello experts,
This is how I match Portal & Tally - Valid only for up to 20,000 rows of data because of the range in the formula. I change the range in the formula if the rows are more than that.
First, I Check the Integrated Tax column, if it is not in column G, then I shift it to column G. Then, Sort data by "Integrated Tax" > largest to smallest. Enter formula in J2 with control + Shift + Enter

=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""),MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000="PORTAL"),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000="TALLY"),1,0)))),"Matched",NA()),NA())

Drag down only till the cell values are >0 in "Integrated Tax" column. Copy the result and paste it back with paste special to remove the formulas. Next I shift the Central Tax column to G2. Then again, Sort data by Central Tax > largest to smallest. Enter formula in J2 with control + Shift + Enter

=IFERROR(IF(ROW(B2)<=SMALL(IF((ABS(G2-$G$2:$G$20000)<=1)*(C2=$C$2:$C$20000)*(B2=$B$2:$B$20000),ROW($A$2:$A$20000),""),MIN(SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000="PORTAL"),1,0)),SUM(IF(($C$2:$C$20000=C2)*(ABS(G2-$G$2:$G$20000)<=1)*($B$2:$B$20000="TALLY"),1,0)))),"Matched",NA()),NA())

Drag down only till the cell values are >0 in "Central Tax" column. Copy the result and paste it back with paste special to remove the formulas. I change the contents of the column J2 from formula to values, which I do by copying the whole column and pasting it back with paste special values. There are instances when there are blanks in both IGST & CGST. Those too will be treated as mismatches. Finally, without deleting the Edited Portal sheet, I have to divide it into 2 sheets. One with matches and the other with mismatches, i.e., errors. In this case, as it is a sample data there is just only one mismatch. In original data, up to 20% of the data may have mismatches. This will complete 80% of the manual work involved. With your help & expertise
Please note: There is a possibility that the Invoice Date and Invoice Number may be different in Tally from Portal in most of the cases. Most of the Invoice number could be a partial match. The dates may be an exact match to a certain extent (50%). So, it is not a good idea to consider these columns to match between the two.
The CGST & CGST amount are always the same (99.99%).
The only common values between the 2 is the GSTIN number.
Thank you in advance.
Code to Match Portal & Tally.xlsm

 

[johnnyL]

First, I Check the Integrated Tax column, if it is not in column G, then I shift it to column G.

What do you mean by 'shift it'?

 

[RAJESH1960]

I mean cut and paste. The formula I apply is directed to column G. So, to match IGST first, I shift the column IGST to column G by selecting the column + control X, to cut the column and +insert than before H with control + to paste the column. Same in the case when I match CGST.

 

[RAJESH1960]

I somehow feel, that will not be needed in the code as you will be doing something like if IGST & CGST = blank then show blank, if IGST > 0 then match IGST portal with IGST Tally else match CGST portal with CGST tally. Just imagined.

 

[johnnyL]

When would Integrated Tax not be the G column? If it wasn't in the G column then your header row would be wrong. This is being done on the 'Edited Portal' sheet, right?

 

[RAJESH1960]

First, I Check the Integrated Tax column, if it is not in column G, then I shift it to column G.

With your code, it will be in column G. But by chance, you never know.....You can avoid that. I think it will not be necessary as the code by default will display Integrated tax in Column G . But you will have to think about the shifting the column Central Tax.

 

[johnnyL]

What Insanity pill would you take to not use my code?

 

[RAJESH1960]

What Insanity pill would you take to not use my code?

Playing with and editing your code.?

 

[johnnyL]

Fair enough. Can we assume the header row will always be the first row and the titles will remain the same in the header row?

 

[RAJESH1960]

I already did that with one of your codes and now I am stuck very badly. Insanity is the right word. ?