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.

 

[RAJESH1960]

This is the workbook where I have matched the data of Portal and tally with Mikerickson's formula. As I said earlier that the formula is matching the multiple amounts under the same GSTIN number, I want it to match only one row of portal and tally. The rows marked yellow are not a match as shown in this sheet.

mikerickson's formula.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Line	As per	GSTIN of supplier	X	Invoice number	Invoice Date	Integrated Tax (₹)	X	X	mikerickson's Formula
2	1	PORTAL	07XXXTB0431R1XD		0000CR2019000893	22-05-2019	900.00			Matched
3	108	TALLY	07XXXTB0431R1XD			22-05-2019	901.00			Matched
4	2	PORTAL	07XXXTB0431R1XD		0000CR2019001162	05-06-2019	900.00			Matched
5	3	PORTAL	07XXXTB0431R1XD		0000CR2019001163	05-06-2019	900.00			Matched
6	4	PORTAL	07XXXTB0431R1XD		0000CR2019001164	05-06-2019	900.00			Matched
7	109	TALLY	07XXXTB0431R1XD			05-06-2019	899.00			Matched
8	110	TALLY	07XXXTB0431R1XD		0000CR2019001163	05-06-2019	900.00			Matched
9	111	TALLY	07XXXTB0431R1XD			05-06-2019	901.00			Matched
10	5	PORTAL	07XXXTB0431R1XD		0000CR2019001809	12-07-2019	900.00			Matched
11	112	TALLY	07XXXTB0431R1XD			12-07-2019	900.00			Matched
12	6	PORTAL	07XXXTB0431R1XD		0000CR2019002131	26-07-2019	900.00			Matched
13	113	TALLY	07XXXTB0431R1XD			26-07-2019	901.00			Matched
14	7	PORTAL	07XXXTB0431R1XD		0000CR2019002276	03-08-2019	900.00			Matched
15	114	TALLY	07XXXTB0431R1XD			03-08-2019	899.50			Matched
16	8	PORTAL	07XXXTB0431R1XD		0000CR2019003076	19-09-2019	900.00			Matched
17	115	TALLY	07XXXTB0431R1XD			19-09-2019	900.50			Matched
18	9	PORTAL	07XXXTB0431R1XD		0000CR2019003126	23-09-2019	900.00			Matched
19	116	TALLY	07XXXTB0431R1XD		3126	23-09-2019	900.00			Matched
20	10	PORTAL	07XXXTB0431R1XD		0000CR2019005946	28-02-2020	900.00			Matched
21	117	TALLY	07XXXTB0431R1XD			28-02-2020	900.00			Matched
22	11	PORTAL	19XXECS5031Q1XV		GST-065/19-20	06-02-2020	1881.00			Matched
23	118	TALLY	19XXECS5031Q1XV		65	06-02-2020	1881.50			Matched
24	12	PORTAL	19XXECS5031Q1XV		GST-052/19-20	25-12-2019	2520.00			Matched
25	13	PORTAL	19XXECS5031Q1XV		GST-053/19-20	04-01-2020	2520.00			Matched
26	119	TALLY	19XXECS5031Q1XV		53	04-01-2020	2520.00			Matched
27	14	PORTAL	19XXECS5031Q1XV		GST-057/19-20	13-01-2020	2520.00			Matched
28	120	TALLY	19XXECS5031Q1XV		57	13-01-2020	2519.50			Matched
29	121	TALLY	27XXOFD3597R1XC		446	01-01-2020	7520.90			Matched
30	122	TALLY	27XXOFD3597R1XC		447	01-01-2020	7521.00			Matched
31	123	TALLY	27XXOFD3597R1XC		448	01-01-2020	7522.00			Matched
32	124	TALLY	27XXOFD3597R1XC		444	01-01-2020	7521.45			Matched
33	125	TALLY	27XXOFD3597R1XC		445	01-01-2020	7521.45			Matched
34	126	TALLY	27XXOFD3597R1XC		443	01-01-2020	7521.50			Matched
35	15	PORTAL	27XXOFD3597R1XC		DEI-00438/19-20	29-12-2019	7521.58			Matched
36	16	PORTAL	27XXOFD3597R1XC		DEI-00443/19-20	30-12-2019	7521.58			Matched
37	17	PORTAL	27XXOFD3597R1XC		444	30-12-2019	7521.58			Matched
38	18	PORTAL	27XXOFD3597R1XC		DEI-00445/19-20	30-12-2019	7521.58			Matched
39	19	PORTAL	27XXOFD3597R1XC		DEI-00446/19-20	31-12-2019	7521.58			Matched
40	20	PORTAL	27XXOFD3597R1XC		DEI-00447/19-20	31-12-2019	7521.58			Matched
41	21	PORTAL	27XXOFD3597R1XC		448	31-12-2019	7521.58			Matched
Matched

Cell Formulas
Range	Formula
J2:J41	J2	=IF(MATCH(1,((ABS(G2-$G$2:$G$23200)<=1))*(C2=$C$2:$C$23200)*(B2<>$B$2:$B$23200),0),"Matched","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RAJESH1960]

Hello again,
As this query is connected to this post, I am posting it here again with some data and brief explanation.
I am not sure which formula to get the expected result, but I have tried Index and Match but not countifs in the sample data.
Loading Google Sheets

 

[KRice]

I would not want to maintain this formula, but this appears to work. The only discrepancy between the formula results and your expected results are the orange cells, and I believe the order in this case is arbitrary. The formula will tag the first (from the top) as "Matched", while any below it that have no mate will be tagged "Not Found".

MrExcel_20220525_Rajesh_Match using index match or Countifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Line	As Per	GSTIN of supplier	Name	Invoice number	Invoice Date	Integrated Tax	Central Tax	State/UT	Expected Result	New Formula
2
3	1	PORTAL	06XXXCR0846G1RN		176	09-02-2017	3060.00	0.00	0.00	Matched	Matched
4	2	PORTAL	06XXXCR0846G1RN		177	09-02-2017	3060.00	0.00	0.00	Not Found	Not Found
5	3	PORTAL	06XXXCR0846G1RN		80	08-04-2017	1530.00	0.00	0.00	Matched	Matched
6	4	PORTAL	06XXXCR0846G1RN		2	07-05-2017	1530.00	0.00	0.00	Matched	Matched
7	5	PORTAL	05XXBCN7151P1R5		173200144	26-07-2017	34125.00	0.00	0.00	Matched	Matched
8	6	PORTAL	05XXBCN7151P1R5		173200143	26-07-2017	73990.00	0.00	0.00	Matched	Matched
9	7	PORTAL	05XXBCN7151P1R5		173201277	11-09-2017	34125.00	0.00	0.00	Not Found	Not Found
10	8	PORTAL	03XXXCH5841B1R8		S-196	12-11-2017	1530.00	0.00	0.00	Matched	Matched
11	9	PORTAL	05XXBCN7151P1R5		173201690	15-12-2017	52668.00	0.00	0.00	Matched	Matched
12	10	PORTAL	03XXXCH5841B1R8		G-626	22-02-2018	54900.00	0.00	0.00	Matched	Matched
13	11	PORTAL	03XXXCH5841B1R8		S-299	26-02-2018	1530.00	0.00	0.00	Not Found	Not Found
14	12	PORTAL	03XXXCH5841B1R8		S-308	27-02-2018	990.00	0.00	0.00	Matched	Matched
15	13	PORTAL	03XXXCH5841B1R8		S-359	28-03-2018	2700.00	0.00	0.00	Matched	Matched
16	14	PORTAL	03XXXCH5841B1R8		G-53	01-04-2018	8820.00	0.00	0.00	Not Found	Matched
17	15	PORTAL	03XXXCH5841B1R8		G-665	03-07-2018	26190.00	0.00	0.00	Matched	Matched
18	16	PORTAL	03XXXCH5841B1R8		G-531	01-12-2018	8820.00	0.00	0.00	Matched	Not Found
19	1	TALLY	05XXBCN7151P1R5			08-01-2017	34125.00	0.00	0.00	Matched	Matched
20	2	TALLY	05XXBCN7151P1R5		173200143	08-01-2017	73990.00	0.00	0.00	Matched	Matched
21	3	TALLY	06XXXCR0846G1RN		264	10-01-2017	52668.00	0.00	0.00	Not Found	Not Found
22	4	TALLY	06XXXCR0846G1RN			09-02-2017	3059.50	0.00	0.00	Matched	Matched
23	5	TALLY	06XXXCR0846G1RN		80	08-04-2017	1530.50	0.00	0.00	Matched	Matched
24	6	TALLY	06XXXCR0846G1RN			08-04-2017	1531.00	0.00	0.00	Matched	Matched
25	7	TALLY	06XXXCR0846G1RN			07-05-2017	1530.00	0.00	0.00	Not Found	Not Found
26	8	TALLY	05XXBCN7151P1R5			11-09-2017	9360.00	0.00	0.00	Not Found	Not Found
27	9	TALLY	05XXBCN7151P1R5		173201690	15-12-2017	52668.00	0.00	0.00	Matched	Matched
28	10	TALLY	03XXXCH5841B1R8			03-01-2018	990.00	0.00	0.00	Matched	Matched
29	11	TALLY	03XXXCH5841B1R8			03-01-2018	1530.00	0.00	0.00	Matched	Matched
30	12	TALLY	03XXXCH5841B1R8		G0626	03-01-2018	54899.50	0.00	0.00	Matched	Matched
31	13	TALLY	03XXXCH5841B1R8		G0627	03-01-2018	54900.50	0.00	0.00	Not Found	Not Found
32	14	TALLY	03XXXCH5841B1R8			28-03-2018	2700.00	0.00	0.00	Matched	Matched
33	15	TALLY	03XXXCH5841B1R8		G0665	03-07-2018	26189.50	0.00	0.00	Matched	Matched
34	16	TALLY	03XXXCH5841B1R8		G0531	01-12-2018	8820.50	0.00	0.00	Matched	Matched
Matched

Cell Formulas
Range	Formula
K3:K34	K3	= IF(SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Portal"=$B$3:$B$34)) = SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Tally"=$B$3:$B$34) ), "Matched", IF(SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Portal"=$B$3:$B$34)) > SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Tally"=$B$3:$B$34) ), IF(SUM( (ABS(G3-$G$3:$G3)<=1) * (C3=$C$3:$C3) * (B3=$B$3:$B3) ) <= SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Tally"=$B$3:$B$34) ), "Matched", "Not Found"), IF(SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Tally"=$B$3:$B$34) ) > SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Portal"=$B$3:$B$34) ), IF(SUM( (ABS(G3-$G$3:$G3)<=1) * (C3=$C$3:$C3) * (B3=$B$3:$B3) ) <= SUM( (ABS(G3-$G$3:$G$34)<=1) * (C3=$C$3:$C$34) * ("Portal"=$B$3:$B$34) ),"Matched", "Not Found"))))

 

[RAJESH1960]

whoopee!! That is one of the biggest formula I have seen. You got the maximum number of Matches possible. That is 90% of the work done and it will save a lot of time when matching huge data base with 20000 or more rows. I can, manually check the rest of the data to check the not found.
You are right about this. I couldn't get the right words to explain what you mentioned in the below message. For that I will sort the data by date and then enter the formula. That way it will become more accurate. Right.

The formula will tag the first (from the top) as "Matched", while any below it that have no mate will be tagged "Not Found".

There is but one problem. The formula should not show not found rows as Matched rows in any case. In this case you formula shows rows 2, 3 and 21 as matches whereas it has to show only 2 and 21 or 3 and 21. There has to be always one set of rows to match. In this case it is 3 rows. If you can correct this, it would just be great.

 

[RAJESH1960]

I can't view your row 2 values. In your case, it is rows 3, 4 and 22

 

[RAJESH1960]

If you remember I had the same problem a year ago. You just added +1 in the formula and solved it. I assume it was for if common amounts take one and again if common amounts take the next one.

=IF(M2="","",IFERROR(INDEX($A$3:$I$2000,AGGREGATE(15,6,(ROW($C$3:$C$2000)-ROW($C$2))/($C$3:$C$2000=$M3),INT((COLUMNS($O3:P3)+1)/2)),9),""))

But this was for a different project which was completely different.

 

[RAJESH1960]

+1)/2)),9),""

This is the place where you edited and added +1

 

[KRice]

I don't understand your point. My formula matches your expected results except for rows 16 and 18, and I believe it is arbitrary which of those would be labeled first as matched. Can you explain why you believe there is an issue with 3,4, and 22? 3 matches 22 and that leaves 4 unmatched...therefore "Not found". Those results appear to be consistent with your expected results too.

 

[RAJESH1960]

Line 3 and 22 should show as matched and line 4 should be Not found. If you copy the matched amounts rows to a different sheet and sub total Portal and Tally you will find the difference.
I will send the sub total sheet in some time.