formula to get the perfect matches between 2 rows

[RAJESH1960]

Hello Guys,
I have this sheet where I have to get the Matches and Mismatches between Portal and Tally. Till now, I was using the formula shared by Mr. mikerickson, but today I noticed that when there are same or approximate amounts with a difference upto+1 or -1, which appear twice under the same GSTIN number, I was getting some mismatched amounts as matched. Earlier, when mikerickson shared the formula, I didn't share the columns of Invoice Number and Invoice Date as they were not always an exact match. Hope these columns are helpful in correcting the formula.
I need help to correct this formula and get the perfect matches. I am not sure but maybe a countif / s formula may solve the problem.
Sorry posted the wrong excel sheet. will post the right one in some time.

 

[KRice]

With no blank row, this what my formula looks like in cell K2:

=  IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33))  =  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33))  >  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33) )   >  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33) ),"Matched", "Not Found"))))

If your table does not end at row 33, then all of those instances of "33" in the formula need to be revised.

 

[RAJESH1960]

Got it KRice. I understood you well. Trying out again with the below formula

With no blank row, this what my formula looks like in cell K2:

=  IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33))  =  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33))  >  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Tally"=$B$2:$B$33) )   >  SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$33)<=1) * (C2=$C$2:$C$33) * ("Portal"=$B$2:$B$33) ),"Matched", "Not Found"))))

If your table does not end at row 33, then all of those instances of "33" in the formula need to be revised.

 

[RAJESH1960]

As the formula range is freezed, it will copy the same range.
One more last question, if there are 20,000 or more rows, I will edit the range in the formula from 34 to 20,000. The question is if the range is different say 10,000, or 15,000 do I have to change it in the formula each time or the range for 20,000 will work for all the data which will have rows less than 20,000

 

[RAJESH1960]

I tried and it worked.

=  IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  =  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched",
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200))  >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200)  ), "Matched", "Not Found"),
    IF(SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Tally"=$B$2:$B$23200) )   >  SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),
    IF(SUM( (ABS(G2-$G$2:$G2)<=1)   * (C2=$C$2:$C2)   * (B2=$B$2:$B2)        )  <= SUM( (ABS(G2-$G$2:$G$23200)<=1) * (C2=$C$2:$C$23200) * ("Portal"=$B$2:$B$23200) ),"Matched", "Not Found"))))

I hope I don't face any issues when I use it in the original data.

 

[KRice]

The formula should work okay with an overly large range. But you don't need to manually edit each number. Just highlight the entire column where the formula appears and go to Home > Find & Select dropdown and then choose > Replace. Then change 33 or 34 to 1000 or 10000 or whatever you want.

 

[RAJESH1960]

I did the same way you said.
KRice. You are the best formula expert I have come across. I have waited for this formula for more than 6 months now. Somehow, I felt that after your last formula you shared a year ago, you can do this and you did it man.
Thanks a lot.

The formula should work okay with an overly large range. But you don't need to manually edit each number. Just highlight the entire column where the formula appears and go to Home > Find & Select dropdown and then choose > Replace. Then change 33 or 34 to 1000 or 10000 or whatever you want.

 

[KRice]

I'm happy to help.