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

 

[RAJESH1960]

Yes, I will keep all the headers in the same row. Create a button with a code, to clear the data without the headers in the working sheets,

 

[RAJESH1960]

That is when I have to match a new data. Clear old data and paste new data to match.

 

[johnnyL]

Another Question. Are you wanting the 'Matches' & 'Mismatched' sheets to be created, or did you include those sheets for explanation purpose.

 

[RAJESH1960]

You are right. I want them created. I just included them for explanation purpose.

 

[johnnyL]

??? They should or should not be created?

 

[RAJESH1960]

??? They should or should not be created?

They should be created.

 

[RAJESH1960]

created with the headings in both sheets.

 

[johnnyL]

Ok. I think I am grasping what you require.

 

[RAJESH1960]

Ok. I think I am grasping what you require.

Phew!!! ?

 

[johnnyL]

Nope. One more question. You said you put the formula into the J column, copy it back so that just the values remain. You didn't say what you want to do after that, because what you posted says that you copy another formula into the j column which would remove the results from the first formula copied into that column.