Index Match Function within selected Blank Cells

[RAJESH1960]

Hello experts

As the invoice number in TALLY are not correct, I have to enter and the matching invoice number in TALLY rows with the PORTAL invoice number in column E. The only common thing between them is the GSTIN number and TRADE NAME. The date of tally is equal to PORTAL invoice date and greater than portal in most of the cases. Even the Invoice value in tally may be equal or less or more than the PORTAL Invoice value with a nominal difference of less than +1 or -1. I have to select the blank cells in column E and insert maybe a index match function which I am not so good at. If anyone can help me to write the formula, I would really appreciate it.

practice 2A match 23082021.xlsx
	A	B	C	D	E	F	G	H
1	Line	As Per	GSTIN of supplier	Trade/Legal name	Invoice number	Invoice numberxx	Invoice Date	Invoice Value(₹)
2	706	PORTAL	24BGZPS0000F1Z0	January	14	14	04-09-2020	2950.00
3	1807	TALLY	24BGZPS0000F1Z0	January		464	04-09-2020	2950.00
4	832	PORTAL	07EUDPS1111F1ZF	February	2758	2758	02-07-2020	10500.00
5	1535	TALLY	07EUDPS1111F1ZF	February		2758	02-07-2020	10500.00
6	1120	PORTAL	07EUDPS1111F1ZF	February	3032	3032	26-10-2020	10500.00
7	2074	TALLY	07EUDPS1111F1ZF	February		3032	26-10-2020	10500.00
8	1073	PORTAL	27AHTPJ2222N1ZN	March	S-1498	S-1498	19-01-2021	8118.60
9	2507	TALLY	27AHTPJ2222N1ZN	March		Jan-1498	19-01-2021	8119.00
10	1678	TALLY	27AHTPJ2222N1ZN	March		335	07-08-2020	8819.00
11	503	PORTAL	27AHTPJ2222N1ZN	March	S-226	S-226	10-08-2020	15417.00
12	507	PORTAL	27AHTPJ2222N1ZN	March	S-318	S-318	26-08-2020	15745.00
13	1762	TALLY	27AHTPJ2222N1ZN	March		419	26-08-2020	15745.00
14	506	PORTAL	27AHTPJ2222N1ZN	March	S-303	S-303	24-08-2020	19727.00
15	1747	TALLY	27AHTPJ2222N1ZN	March		404	24-08-2020	19727.00
16	1670	TALLY	27AHTPJ2222N1ZN	March		327	06-08-2020	19733.00
17	501	PORTAL	27AHTPJ2222N1ZN	March	S-206	S-206	06-08-2020	19744.00
18	900	PORTAL	27AHTPJ2222N1ZN	March	S-736	S-736	21-10-2020	20300.00
19	2057	TALLY	27AHTPJ2222N1ZN	March		736	21-10-2020	20300.00
20	1289	PORTAL	27AHTPJ2222N1ZN	March	S-1785	S-1785	22-02-2021	20821.50
21	2649	TALLY	27AHTPJ2222N1ZN	March		Feb-1785	22-02-2021	20822.00
22	2515	TALLY	27AHTPJ2222N1ZN	March		Jan-1505	20-01-2021	21268.00
23	1074	PORTAL	27AHTPJ2222N1ZN	March	S-1505	S-1505	20-01-2021	21268.28
24	759	PORTAL	27AHTPJ2222N1ZN	March	S-1102	S-1102	30-11-2020	21275.00
25	2227	TALLY	27AHTPJ2222N1ZN	March		Dec-1102	01-12-2020	21275.00
26	2318	TALLY	27AHTPJ2222N1ZN	March		Dec-1235	15-12-2020	21322.00
27	586	PORTAL	29AAJFD3333B1ZR	April	KA-2216-20-21	KA-2216-20-21	13-09-2020	32422.00
28	1854	TALLY	29AAJFD3333B1ZR	April		511	13-09-2020	32422.00
29	1460	PORTAL	29AAJFD3333B1ZR	April	KA-9748-20-21	KA-9748-20-21	20-03-2021	34550.00
30	2728	TALLY	29AAJFD3333B1ZR	April		9748	20-03-2021	34550.00
31	583	PORTAL	29AAJFD3333B1ZR	April	KA-2035-20-21	KA-2035-20-21	03-09-2020	37845.00
32	1796	TALLY	29AAJFD3333B1ZR	April		453	03-09-2020	37845.00
33	1518	PORTAL	06ATLPK4444D1ZK	May	1938	1938	22-03-2021	12222.00
34	1519	PORTAL	06ATLPK4444D1ZK	May	1939	1939	22-03-2021	12222.00
35	2733	TALLY	06ATLPK4444D1ZK	May		1939	22-03-2021	12222.00
36	2734	TALLY	06ATLPK4444D1ZK	May		1938	22-03-2021	12222.00
37	527	PORTAL	06ATLPK4444D1ZK	May	582	582	19-09-2020	26775.00
38	1894	TALLY	06ATLPK4444D1ZK	May		551	19-09-2020	26775.00
39	1896	TALLY	06ATLPK4444D1ZK	May		553	19-09-2020	26775.00
40	2433	TALLY	06ATLPK4444D1ZK	May		Jan-1594	05-01-2021	26880.00
41	1062	PORTAL	06ATLPK4444D1ZK	May	1594	1594	08-01-2021	26880.00
42	809	PORTAL	06ATLPK4444D1ZK	May	1302	1302	17-12-2020	27300.00
43	2338	TALLY	06ATLPK4444D1ZK	May		Dec-1302	17-12-2020	27300.00
44	1065	PORTAL	06ATLPK4444D1ZK	May	1642	1642	12-01-2021	66150.00
45	1066	PORTAL	06ATLPK4444D1ZK	May	1643	1643	12-01-2021	66150.00
46	1067	PORTAL	06ATLPK4444D1ZK	May	1644	1644	12-01-2021	66150.00
47	2470	TALLY	06ATLPK4444D1ZK	May		Jan-1642	12-01-2021	66150.00
48	2471	TALLY	06ATLPK4444D1ZK	May		Jan-1643	12-01-2021	66150.00
49	2472	TALLY	06ATLPK4444D1ZK	May		Jan-1644	12-01-2021	66150.00
50	1563	TALLY	29AADCF5555H1ZU	June		157	07-07-2020	355.00
51	16	PORTAL	29AADCF5555H1ZU	June	157	157	08-07-2020	355.00
52	800	PORTAL	29AADCF5555H1ZU	July	RI/20-21/529	RI/20-21/529	28-11-2020	21000.00
53	2229	TALLY	29AADCF5555H1ZU	July		Dec-529	01-12-2020	21000.00
54	2608	TALLY	29AADCF5555H1ZU	July		Feb-641	09-02-2021	22722.00
55	1430	PORTAL	29AADCF5555H1ZU	July	RI/20-21/641	RI/20-21/641	10-02-2021	22722.00
56	1110	PORTAL	29AADCF5555H1ZU	July	RI/20-21/571	RI/20-21/571	23-12-2020	25284.00
57	2370	TALLY	29AADCF5555H1ZU	July		Dec-571	23-12-2020	25284.00
58	1528	TALLY	33AAACY6666M1Z2	August		1155	01-07-2020	2950.00
59	417	PORTAL	33AAACY6666M1Z2	August	CBE0123	CBE0123	07-07-2020	2950.00
seperate error rows

Cell Formulas
Range	Formula
E2:E59	E2	=IF(B2="portal",F2,"")

 

[RAJESH1960]

I am trying this since once month with various formulas but I am sure I will get it right one day. The only problem is I am not at all good at explaining the query. Time to hit the sack. Marcelo Branco will meet again tomorrow. Good luck.

 

[Marcelo Branco]

hmm... i'm confused

I tried a formula and got most of the expected results, but in some cases it produced a different results from yours - see column J (Test) the results in yellow)

Are really these results wrong? If, so why?

Pasta1
	A	B	C	D	E	F	G	H	I	J
1	Line	As Per	GSTIN of supplier	Trade/Legal name	Invoice number	Invoice numberxx	Invoice Date	Invoice Value(₹)		Test
2	706	PORTAL	24BGZPS0000F1Z0	January	14	14	44078	2950		14
3	1807	TALLY	24BGZPS0000F1Z0	January	14	464	44078	2950		14
4	832	PORTAL	07EUDPS1111F1ZF	February	2758	2758	44014	10500		2758
5	1535	TALLY	07EUDPS1111F1ZF	February	2758	2758	44014	10500		2758
6	1120	PORTAL	07EUDPS1111F1ZF	February	3032	3032	44130	10500		3032
7	2074	TALLY	07EUDPS1111F1ZF	February	3032	3032	44130	10500		3032
8	1073	PORTAL	27AHTPJ2222N1ZN	March	S-1498	S-1498	44215	8118,6		S-1498
9	2507	TALLY	27AHTPJ2222N1ZN	March	S-1498	Jan-1498	44215	8119		S-1498
10	1678	TALLY	27AHTPJ2222N1ZN	March	335	335	44050	8819		335
11	503	PORTAL	27AHTPJ2222N1ZN	March	S-226	S-226	44053	15417		S-226
12	507	PORTAL	27AHTPJ2222N1ZN	March	S-318	S-318	44069	15745		S-318
13	1762	TALLY	27AHTPJ2222N1ZN	March	S-318	419	44069	15745		S-318
14	506	PORTAL	27AHTPJ2222N1ZN	March	S-303	S-303	44067	19727		S-303
15	1747	TALLY	27AHTPJ2222N1ZN	March	S-303	404	44067	19727		S-303
16	1670	TALLY	27AHTPJ2222N1ZN	March	S-206	327	44049	19733		S-206
17	501	PORTAL	27AHTPJ2222N1ZN	March	S-206	S-206	44049	19744		S-206
18	900	PORTAL	27AHTPJ2222N1ZN	March	S-736	S-736	44125	20300		S-736
19	2057	TALLY	27AHTPJ2222N1ZN	March	S-736	736	44125	20300		S-736
20	1289	PORTAL	27AHTPJ2222N1ZN	March	S-1785	S-1785	44249	20821,5		S-1785
21	2649	TALLY	27AHTPJ2222N1ZN	March	S-1785	Feb-1785	44249	20822		S-1785
22	2515	TALLY	27AHTPJ2222N1ZN	March	S-1505	Jan-1505	44216	21268		S-1505
23	1074	PORTAL	27AHTPJ2222N1ZN	March	S-1505	S-1505	44216	21268,28		S-1505
24	759	PORTAL	27AHTPJ2222N1ZN	March	S-1102	S-1102	44165	21275		S-1102
25	2227	TALLY	27AHTPJ2222N1ZN	March	S-1102	Dec-1102	44166	21275		Dec-1102
26	2318	TALLY	27AHTPJ2222N1ZN	March	Dec-1235	Dec-1235	44180	21322		Dec-1235
27	586	PORTAL	29AAJFD3333B1ZR	April	KA-2216-20-21	KA-2216-20-21	44087	32422		KA-2216-20-21
28	1854	TALLY	29AAJFD3333B1ZR	April	KA-2216-20-21	511	44087	32422		KA-2216-20-21
29	1460	PORTAL	29AAJFD3333B1ZR	April	KA-9748-20-21	KA-9748-20-21	44275	34550		KA-9748-20-21
30	2728	TALLY	29AAJFD3333B1ZR	April	KA-9748-20-21	9748	44275	34550		KA-9748-20-21
31	583	PORTAL	29AAJFD3333B1ZR	April	KA-2035-20-21	KA-2035-20-21	44077	37845		KA-2035-20-21
32	1796	TALLY	29AAJFD3333B1ZR	April	KA-2035-20-21	453	44077	37845		KA-2035-20-21
33	1518	PORTAL	06ATLPK4444D1ZK	May	1938	1938	44277	12222		1938
34	1519	PORTAL	06ATLPK4444D1ZK	May	1939	1939	44277	12222		1939
35	2733	TALLY	06ATLPK4444D1ZK	May	1938	1939	44277	12222		1938
36	2734	TALLY	06ATLPK4444D1ZK	May	1939	1938	44277	12222		1938
37	527	PORTAL	06ATLPK4444D1ZK	May	582	582	44093	26775		582
38	1894	TALLY	06ATLPK4444D1ZK	May	582	551	44093	26775		582
39	1896	TALLY	06ATLPK4444D1ZK	May	553	553	44093	26775		582
40	2433	TALLY	06ATLPK4444D1ZK	May	1594	Jan-1594	44201	26880		Jan-1594
41	1062	PORTAL	06ATLPK4444D1ZK	May	1594	1594	44204	26880		1594
42	809	PORTAL	06ATLPK4444D1ZK	May	1302	1302	44182	27300		1302
43	2338	TALLY	06ATLPK4444D1ZK	May	1302	Dec-1302	44182	27300		1302
44	1065	PORTAL	06ATLPK4444D1ZK	May	1642	1642	44208	66150		1642
45	1066	PORTAL	06ATLPK4444D1ZK	May	1643	1643	44208	66150		1643
46	1067	PORTAL	06ATLPK4444D1ZK	May	1644	1644	44208	66150		1644
47	2470	TALLY	06ATLPK4444D1ZK	May	1642	Jan-1642	44208	66150		1642
48	2471	TALLY	06ATLPK4444D1ZK	May	1643	Jan-1643	44208	66150		1642
49	2472	TALLY	06ATLPK4444D1ZK	May	1644	Jan-1644	44208	66150		1642
50	1563	TALLY	29AADCF5555H1ZU	June	157	157	44019	355		157
51	16	PORTAL	29AADCF5555H1ZU	June	157	157	44020	355		157
52	800	PORTAL	29AADCF5555H1ZU	July	RI/20-21/529	RI/20-21/529	44163	21000		RI/20-21/529
53	2229	TALLY	29AADCF5555H1ZU	July	RI/20-21/529	Dec-529	44166	21000		Dec-529
54	2608	TALLY	29AADCF5555H1ZU	July	RI/20-21/641	Feb-641	44236	22722		Feb-641
55	1430	PORTAL	29AADCF5555H1ZU	July	RI/20-21/641	RI/20-21/641	44237	22722		RI/20-21/641
56	1110	PORTAL	29AADCF5555H1ZU	July	RI/20-21/571	RI/20-21/571	44188	25284		RI/20-21/571
57	2370	TALLY	29AADCF5555H1ZU	July	RI/20-21/571	Dec-571	44188	25284		RI/20-21/571
58	1528	TALLY	33AAACY6666M1Z2	August	CBE0123	1155	44013	2950		1155
59	417	PORTAL	33AAACY6666M1Z2	August	CBE0123	CBE0123	44019	2950		CBE0123
Plan11

Cell Formulas
Range	Formula
E10,E39,E26	E10	=F10
J2:J59	J2	=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$59,AGGREGATE(15,6,(ROW(F$2:F$59)-ROW(F$2)+1)/((B$2:B$59="PORTAL")*(C$2:C$59=C2)*(G$2:G$59=G2)),1))),F2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J59	Expression	=J2<>E2	text	NO

M.

 

[RAJESH1960]

hmm... i'm confused

I tried a formula and got most of the expected results, but in some cases it produced a different results from yours - see column J (Test) the results in yellow)

Are really these results wrong? If, so why?

Pasta1
	A	B	C	D	E	F	G	H	I	J
1	Line	As Per	GSTIN of supplier	Trade/Legal name	Invoice number	Invoice numberxx	Invoice Date	Invoice Value(₹)		Test
2	706	PORTAL	24BGZPS0000F1Z0	January	14	14	44078	2950		14
3	1807	TALLY	24BGZPS0000F1Z0	January	14	464	44078	2950		14
4	832	PORTAL	07EUDPS1111F1ZF	February	2758	2758	44014	10500		2758
5	1535	TALLY	07EUDPS1111F1ZF	February	2758	2758	44014	10500		2758
6	1120	PORTAL	07EUDPS1111F1ZF	February	3032	3032	44130	10500		3032
7	2074	TALLY	07EUDPS1111F1ZF	February	3032	3032	44130	10500		3032
8	1073	PORTAL	27AHTPJ2222N1ZN	March	S-1498	S-1498	44215	8118,6		S-1498
9	2507	TALLY	27AHTPJ2222N1ZN	March	S-1498	Jan-1498	44215	8119		S-1498
10	1678	TALLY	27AHTPJ2222N1ZN	March	335	335	44050	8819		335
11	503	PORTAL	27AHTPJ2222N1ZN	March	S-226	S-226	44053	15417		S-226
12	507	PORTAL	27AHTPJ2222N1ZN	March	S-318	S-318	44069	15745		S-318
13	1762	TALLY	27AHTPJ2222N1ZN	March	S-318	419	44069	15745		S-318
14	506	PORTAL	27AHTPJ2222N1ZN	March	S-303	S-303	44067	19727		S-303
15	1747	TALLY	27AHTPJ2222N1ZN	March	S-303	404	44067	19727		S-303
16	1670	TALLY	27AHTPJ2222N1ZN	March	S-206	327	44049	19733		S-206
17	501	PORTAL	27AHTPJ2222N1ZN	March	S-206	S-206	44049	19744		S-206
18	900	PORTAL	27AHTPJ2222N1ZN	March	S-736	S-736	44125	20300		S-736
19	2057	TALLY	27AHTPJ2222N1ZN	March	S-736	736	44125	20300		S-736
20	1289	PORTAL	27AHTPJ2222N1ZN	March	S-1785	S-1785	44249	20821,5		S-1785
21	2649	TALLY	27AHTPJ2222N1ZN	March	S-1785	Feb-1785	44249	20822		S-1785
22	2515	TALLY	27AHTPJ2222N1ZN	March	S-1505	Jan-1505	44216	21268		S-1505
23	1074	PORTAL	27AHTPJ2222N1ZN	March	S-1505	S-1505	44216	21268,28		S-1505
24	759	PORTAL	27AHTPJ2222N1ZN	March	S-1102	S-1102	44165	21275		S-1102
25	2227	TALLY	27AHTPJ2222N1ZN	March	S-1102	Dec-1102	44166	21275		Dec-1102
26	2318	TALLY	27AHTPJ2222N1ZN	March	Dec-1235	Dec-1235	44180	21322		Dec-1235
27	586	PORTAL	29AAJFD3333B1ZR	April	KA-2216-20-21	KA-2216-20-21	44087	32422		KA-2216-20-21
28	1854	TALLY	29AAJFD3333B1ZR	April	KA-2216-20-21	511	44087	32422		KA-2216-20-21
29	1460	PORTAL	29AAJFD3333B1ZR	April	KA-9748-20-21	KA-9748-20-21	44275	34550		KA-9748-20-21
30	2728	TALLY	29AAJFD3333B1ZR	April	KA-9748-20-21	9748	44275	34550		KA-9748-20-21
31	583	PORTAL	29AAJFD3333B1ZR	April	KA-2035-20-21	KA-2035-20-21	44077	37845		KA-2035-20-21
32	1796	TALLY	29AAJFD3333B1ZR	April	KA-2035-20-21	453	44077	37845		KA-2035-20-21
33	1518	PORTAL	06ATLPK4444D1ZK	May	1938	1938	44277	12222		1938
34	1519	PORTAL	06ATLPK4444D1ZK	May	1939	1939	44277	12222		1939
35	2733	TALLY	06ATLPK4444D1ZK	May	1938	1939	44277	12222		1938
36	2734	TALLY	06ATLPK4444D1ZK	May	1939	1938	44277	12222		1938
37	527	PORTAL	06ATLPK4444D1ZK	May	582	582	44093	26775		582
38	1894	TALLY	06ATLPK4444D1ZK	May	582	551	44093	26775		582
39	1896	TALLY	06ATLPK4444D1ZK	May	553	553	44093	26775		582
40	2433	TALLY	06ATLPK4444D1ZK	May	1594	Jan-1594	44201	26880		Jan-1594
41	1062	PORTAL	06ATLPK4444D1ZK	May	1594	1594	44204	26880		1594
42	809	PORTAL	06ATLPK4444D1ZK	May	1302	1302	44182	27300		1302
43	2338	TALLY	06ATLPK4444D1ZK	May	1302	Dec-1302	44182	27300		1302
44	1065	PORTAL	06ATLPK4444D1ZK	May	1642	1642	44208	66150		1642
45	1066	PORTAL	06ATLPK4444D1ZK	May	1643	1643	44208	66150		1643
46	1067	PORTAL	06ATLPK4444D1ZK	May	1644	1644	44208	66150		1644
47	2470	TALLY	06ATLPK4444D1ZK	May	1642	Jan-1642	44208	66150		1642
48	2471	TALLY	06ATLPK4444D1ZK	May	1643	Jan-1643	44208	66150		1642
49	2472	TALLY	06ATLPK4444D1ZK	May	1644	Jan-1644	44208	66150		1642
50	1563	TALLY	29AADCF5555H1ZU	June	157	157	44019	355		157
51	16	PORTAL	29AADCF5555H1ZU	June	157	157	44020	355		157
52	800	PORTAL	29AADCF5555H1ZU	July	RI/20-21/529	RI/20-21/529	44163	21000		RI/20-21/529
53	2229	TALLY	29AADCF5555H1ZU	July	RI/20-21/529	Dec-529	44166	21000		Dec-529
54	2608	TALLY	29AADCF5555H1ZU	July	RI/20-21/641	Feb-641	44236	22722		Feb-641
55	1430	PORTAL	29AADCF5555H1ZU	July	RI/20-21/641	RI/20-21/641	44237	22722		RI/20-21/641
56	1110	PORTAL	29AADCF5555H1ZU	July	RI/20-21/571	RI/20-21/571	44188	25284		RI/20-21/571
57	2370	TALLY	29AADCF5555H1ZU	July	RI/20-21/571	Dec-571	44188	25284		RI/20-21/571
58	1528	TALLY	33AAACY6666M1Z2	August	CBE0123	1155	44013	2950		1155
59	417	PORTAL	33AAACY6666M1Z2	August	CBE0123	CBE0123	44019	2950		CBE0123
Plan11

Cell Formulas
Range	Formula
E10,E39,E26	E10	=F10
J2:J59	J2	=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$59,AGGREGATE(15,6,(ROW(F$2:F$59)-ROW(F$2)+1)/((B$2:B$59="PORTAL")*(C$2:C$59=C2)*(G$2:G$59=G2)),1))),F2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J59	Expression	=J2<>E2	text	NO

M.

It is almost correct but still a few errors. Please check the yellow cells and remarks column. Accounts is about 100% accuracy. But your formula has brought me nearer to perfection.

Line	As Per	GSTIN of supplier	Trade/Legal name	Invoice number	Invoice numberxx	Invoice Date	Invoice Value(₹)	Remarks
1​	PORTAL	24BGZPS0000F1Z0	January	14	14	04-09-2020​	2950.00
2​	TALLY	24BGZPS0000F1Z0	January	14	464	04-09-2020​	2950.00​
3​	PORTAL	07EUDPS1111F1ZF	February	2758	2758	02-07-2020​	10500.00
4​	TALLY	07EUDPS1111F1ZF	February	2758	2758	02-07-2020​	10500.00​
5​	PORTAL	07EUDPS1111F1ZF	February	3032	3032	26-10-2020​	10500.00
6​	TALLY	07EUDPS1111F1ZF	February	3032	3032	26-10-2020​	10500.00​
7​	PORTAL	27AHTPJ2222N1ZN	March	S-1498	S-1498	19-01-2021​	8118.60
8​	TALLY	27AHTPJ2222N1ZN	March	S-1498	Jan-1498	19-01-2021​	8119.00​
9​	TALLY	27AHTPJ2222N1ZN	March	335	335	07-08-2020​	8819.00​
10​	PORTAL	27AHTPJ2222N1ZN	March	S-226	S-226	10-08-2020​	15417.00
11​	PORTAL	27AHTPJ2222N1ZN	March	S-318	S-318	26-08-2020​	15745.00
12​	TALLY	27AHTPJ2222N1ZN	March	S-318	419	26-08-2020​	15745.00​
13​	PORTAL	27AHTPJ2222N1ZN	March	S-303	S-303	24-08-2020​	19727.00
14​	TALLY	27AHTPJ2222N1ZN	March	S-303	404	24-08-2020​	19727.00​
15​	TALLY	27AHTPJ2222N1ZN	March	S-206	327	06-08-2020​	19733.00​
16​	PORTAL	27AHTPJ2222N1ZN	March	S-206	S-206	06-08-2020​	19744.00
17​	PORTAL	27AHTPJ2222N1ZN	March	S-736	S-736	21-10-2020​	20300.00
18​	TALLY	27AHTPJ2222N1ZN	March	S-736	736	21-10-2020​	20300.00​
19​	PORTAL	27AHTPJ2222N1ZN	March	S-1785	S-1785	22-02-2021​	20821.50
20​	TALLY	27AHTPJ2222N1ZN	March	S-1785	Feb-1785	22-02-2021​	20822.00​
21​	TALLY	27AHTPJ2222N1ZN	March	S-1505	Jan-1505	20-01-2021​	21268.00​
22​	PORTAL	27AHTPJ2222N1ZN	March	S-1505	S-1505	20-01-2021​	21268.28
23​	PORTAL	27AHTPJ2222N1ZN	March	S-1102	S-1102	30-11-2020​	21275.00
24​	TALLY	27AHTPJ2222N1ZN	March	Dec-1102	Dec-1102	01-12-2020​	21275.00​	This is a match hence it should show S-1102
25​	TALLY	27AHTPJ2222N1ZN	March	Dec-1235	Dec-1235	15-12-2020​	21322.00​
26​	PORTAL	29AAJFD3333B1ZR	April	KA-2216-20-21	KA-2216-20-21	13-09-2020​	32422.00
27​	TALLY	29AAJFD3333B1ZR	April	KA-2216-20-21	511	13-09-2020​	32422.00​
28​	PORTAL	29AAJFD3333B1ZR	April	KA-9748-20-21	KA-9748-20-21	20-03-2021​	34550.00
29​	TALLY	29AAJFD3333B1ZR	April	KA-9748-20-21	9748	20-03-2021​	34550.00​
30​	PORTAL	29AAJFD3333B1ZR	April	KA-2035-20-21	KA-2035-20-21	03-09-2020​	37845.00
31​	TALLY	29AAJFD3333B1ZR	April	KA-2035-20-21	453	03-09-2020​	37845.00​
32​	PORTAL	06ATLPK4444D1ZK	May	1938	1938	22-03-2021​	12222.00
33​	PORTAL	06ATLPK4444D1ZK	May	1939	1939	22-03-2021​	12222.00
34​	TALLY	06ATLPK4444D1ZK	May	1938	1939	22-03-2021​	12222.00​	one has to show 1938 and the other 1939
35​	TALLY	06ATLPK4444D1ZK	May	1938	1938	22-03-2021​	12222.00​
36​	PORTAL	06ATLPK4444D1ZK	May	582	582	19-09-2020​	26775.00
37​	TALLY	06ATLPK4444D1ZK	May	582	551	19-09-2020​	26775.00​
38​	TALLY	06ATLPK4444D1ZK	May	582	553	19-09-2020​	26775.00​	These are 2 different invoices of same value. It has to select any one and the other should be F39 value
39​	TALLY	06ATLPK4444D1ZK	May	Jan-1594	Jan-1594	05-01-2021​	26880.00​
40​	PORTAL	06ATLPK4444D1ZK	May	1594	1594	08-01-2021​	26880.00
41​	PORTAL	06ATLPK4444D1ZK	May	1302	1302	17-12-2020​	27300.00
42​	TALLY	06ATLPK4444D1ZK	May	1302	Dec-1302	17-12-2020​	27300.00​
43​	PORTAL	06ATLPK4444D1ZK	May	1642	1642	12-01-2021​	66150.00
44​	PORTAL	06ATLPK4444D1ZK	May	1643	1643	12-01-2021​	66150.00
45​	PORTAL	06ATLPK4444D1ZK	May	1644	1644	12-01-2021​	66150.00
46​	TALLY	06ATLPK4444D1ZK	May	1642	Jan-1642	12-01-2021​	66150.00​
47​	TALLY	06ATLPK4444D1ZK	May	1642	Jan-1643	12-01-2021​	66150.00​	These are 3 different invoices of same value.
48​	TALLY	06ATLPK4444D1ZK	May	1642	Jan-1644	12-01-2021​	66150.00​	These are 3 different invoices of same value.
49​	TALLY	29AADCF5555H1ZU	June	157	157	07-07-2020​	355.00​
50​	PORTAL	29AADCF5555H1ZU	June	157	157	08-07-2020​	355.00
51​	PORTAL	29AADCF5555H1ZU	July	RI/20-21/529	RI/20-21/529	28-11-2020​	21000.00
52​	TALLY	29AADCF5555H1ZU	July	Dec-529	Dec-529	01-12-2020​	21000.00​	This is a match hence it should show RI/20-21/529
53​	TALLY	29AADCF5555H1ZU	July	Feb-641	Feb-641	09-02-2021​	22722.00​	This is a match hence it should show RI/20-21/641
54​	PORTAL	29AADCF5555H1ZU	July	RI/20-21/641	RI/20-21/641	10-02-2021​	22722.00
55​	PORTAL	29AADCF5555H1ZU	July	RI/20-21/571	RI/20-21/571	23-12-2020​	25284.00
56​	TALLY	29AADCF5555H1ZU	July	RI/20-21/571	Dec-571	23-12-2020​	25284.00​
57​	TALLY	33AAACY6666M1Z2	August	1155	1155	01-07-2020​	2950.00​	This is a match hence it should show CBE0123
58​	PORTAL	33AAACY6666M1Z2	August	CBE0123	CBE0123	07-07-2020​	2950.00

 

[RAJESH1960]

Regarding row 39, there are 2 entries in Tally and only one in portal. So match one invoice with the same invoice number and the other give the same value. As for row 43, 44 and 45 are a match as they are in row 46, 47 and 48. but you have given 46 to all the invoices in tally.

 

[Marcelo Branco]

To me Row 24 doesn't seem to match with Row 23 since invoice dates are different. Am i wrong?

M.

 

[RAJESH1960]

Hello Marcelo Branco, I have been able to get the maximum matches till now but not all. I am sharing the sheet with the errors. Please see if you can edit and add some additional conditions within the formula. Till now, I have tried every possible way to get it 100% right but to no avail.
I have sorted the data differently this time and I have been able to get some errors rectified.

Get Invoice Number.xlsx
	A	B	C	D	E	F	G	H	I
1	Line	As Per	GSTIN of supplier	Name	common	Invoice numberxx	Invoice Date	Invoice Value	Errors to correct
2	23	PORTAL	29AAJFD3333B1ZR	April	KA-2216-20-21	KA-2216-20-21	13-09-2020	32422.00
3	53	TALLY	29AAJFD3333B1ZR	April	KA-2216-20-21	511	13-09-2020	32422.00
4	24	PORTAL	29AAJFD3333B1ZR	April	KA-9748-20-21	KA-9748-20-21	20-03-2021	34550.00
5	54	TALLY	29AAJFD3333B1ZR	April	KA-9748-20-21	9748	20-03-2021	34550.00
6	25	PORTAL	29AAJFD3333B1ZR	April	KA-2035-20-21	KA-2035-20-21	03-09-2020	37845.00
7	55	TALLY	29AAJFD3333B1ZR	April	KA-2035-20-21	453	03-09-2020	37845.00
8	30	TALLY	33AAACY6666M1Z2	August	453	1155	01-07-2020	2950.00
9	2	PORTAL	33AAACY6666M1Z2	August	CBE0123	CBE0123	07-07-2020	2950.00
10	5	PORTAL	07EUDPS1111F1ZF	February	2758	2758	02-07-2020	10500.00
11	34	TALLY	07EUDPS1111F1ZF	February	2758	2758	02-07-2020	10500.00
12	6	PORTAL	07EUDPS1111F1ZF	February	3032	3032	26-10-2020	10500.00
13	35	TALLY	07EUDPS1111F1ZF	February	3032	3032	26-10-2020	10500.00
14	3	PORTAL	24BGZPS0000F1Z0	January	14	14	04-09-2020	2950.00
15	31	TALLY	24BGZPS0000F1Z0	January	14	464	04-09-2020	2950.00
16	15	PORTAL	29AADCF5555H1ZU	July	RI/20-21/529	RI/20-21/529	28-11-2020	21000.00
17	43	TALLY	29AADCF5555H1ZU	July	RI/20-21/529	Dec-529	01-12-2020	21000.00
18	47	TALLY	29AADCF5555H1ZU	July	Dec-529	Feb-641	09-02-2021	22722.00	Portal below Tally - but an exact match - display RI/20-21/641
19	18	PORTAL	29AADCF5555H1ZU	July	RI/20-21/641	RI/20-21/641	10-02-2021	22722.00
20	19	PORTAL	29AADCF5555H1ZU	July	RI/20-21/571	RI/20-21/571	23-12-2020	25284.00
21	48	TALLY	29AADCF5555H1ZU	July	RI/20-21/571	Dec-571	23-12-2020	25284.00
22	29	TALLY	29AADCF5555H1ZU	June	Dec-571	157	07-07-2020	355.00	Portal below Tally - but an exact match - display 157
23	1	PORTAL	29AADCF5555H1ZU	June	157	157	08-07-2020	355.00
24	4	PORTAL	27AHTPJ2222N1ZN	March	S-1498	S-1498	19-01-2021	8118.60
25	32	TALLY	27AHTPJ2222N1ZN	March	S-1498	Jan-1498	19-01-2021	8119.00
26	33	TALLY	27AHTPJ2222N1ZN	March	Jan-1498	335	07-08-2020	8819.00	Not in portal - display same invoice number 335
27	9	PORTAL	27AHTPJ2222N1ZN	March	S-226	S-226	10-08-2020	15417.00
28	10	PORTAL	27AHTPJ2222N1ZN	March	S-318	S-318	26-08-2020	15745.00
29	38	TALLY	27AHTPJ2222N1ZN	March	S-318	419	26-08-2020	15745.00
30	11	PORTAL	27AHTPJ2222N1ZN	March	S-303	S-303	24-08-2020	19727.00
31	39	TALLY	27AHTPJ2222N1ZN	March	S-303	404	24-08-2020	19727.00
32	12	PORTAL	27AHTPJ2222N1ZN	March	S-206	S-206	06-08-2020	19733.00
33	40	TALLY	27AHTPJ2222N1ZN	March	S-206	327	06-08-2020	19733.00
34	13	PORTAL	27AHTPJ2222N1ZN	March	S-736	S-736	21-10-2020	20300.00
35	41	TALLY	27AHTPJ2222N1ZN	March	S-736	736	21-10-2020	20300.00
36	14	PORTAL	27AHTPJ2222N1ZN	March	S-1785	S-1785	22-02-2021	20821.50
37	42	TALLY	27AHTPJ2222N1ZN	March	S-1785	Feb-1785	22-02-2021	20822.00
38	44	TALLY	27AHTPJ2222N1ZN	March	Feb-1785	Jan-1505	20-01-2021	21268.00	Portal below Tally - but an exact match - display S-1505
39	16	PORTAL	27AHTPJ2222N1ZN	March	S-1505	S-1505	20-01-2021	21268.28
40	17	PORTAL	27AHTPJ2222N1ZN	March	S-1102	S-1102	30-11-2020	21275.00
41	45	TALLY	27AHTPJ2222N1ZN	March	S-1102	Dec-1102	01-12-2020	21275.00
42	46	TALLY	27AHTPJ2222N1ZN	March	Dec-1102	Dec-1235	15-12-2020	21322.00	Not in portal - display same invoice number Dec-1235
43	7	PORTAL	06ATLPK4444D1ZK	May	1938	1938	22-03-2021	12222.00
44	8	PORTAL	06ATLPK4444D1ZK	May	1939	1939	22-03-2021	12222.00
45	36	TALLY	06ATLPK4444D1ZK	May	1939	1939	22-03-2021	12222.00
46	37	TALLY	06ATLPK4444D1ZK	May	1939	1938	22-03-2021	12222.00	same value invoices with different invoice number - display 1938
47	20	PORTAL	06ATLPK4444D1ZK	May	582	582	19-09-2020	26775.00
48	49	TALLY	06ATLPK4444D1ZK	May	582	551	19-09-2020	26775.00
49	50	TALLY	06ATLPK4444D1ZK	May	551	553	19-09-2020	26775.00
50	51	TALLY	06ATLPK4444D1ZK	May	553	Jan-1594	05-01-2021	26880.00
51	21	PORTAL	06ATLPK4444D1ZK	May	1594	1594	08-01-2021	26880.00
52	22	PORTAL	06ATLPK4444D1ZK	May	1302	1302	17-12-2020	27300.00
53	52	TALLY	06ATLPK4444D1ZK	May	1302	Dec-1302	17-12-2020	27300.00
54	26	PORTAL	06ATLPK4444D1ZK	May	1642	1642	12-01-2021	66150.00
55	27	PORTAL	06ATLPK4444D1ZK	May	1643	1643	12-01-2021	66150.00
56	28	PORTAL	06ATLPK4444D1ZK	May	1644	1644	12-01-2021	66150.00
57	56	TALLY	06ATLPK4444D1ZK	May	1644	Jan-1642	12-01-2021	66150.00	same value invoices with different invoice number - display 1642
58	57	TALLY	06ATLPK4444D1ZK	May	Jan-1642	Jan-1643	12-01-2021	66150.00	same value invoices with different invoice number - display 1643
59	58	TALLY	06ATLPK4444D1ZK	May	Jan-1643	Jan-1644	12-01-2021	66150.00	same value invoices with different invoice number - display 1644
Mr.Excel

Cell Formulas
Range	Formula
E2:E59	E2	=IF(B2="PORTAL",F2,IF(COUNTIFS(B:B,"PORTAL",D:D,D2,C:C,C2),F1,F2))

 

[RAJESH1960]

I even tried converting it into a table and there too I am getting so many errors.

I need help to add a few conditions to the formula '=IF(B2="PORTAL",F2,IF(COUNTIFS(B:B,"PORTAL",D:D,D2,C:C,C2),F1,F2)). I think if a countifs condition '=COUNTIFS($C:$D,$C1,$D:$D,$D1)>1 is added within a formula to get the repeated invoice numbers and also a countifs function for the amounts '=COUNTIFS($H$2:$H$15000,H2)>1, would do the trick.

 

[Marcelo Branco]

Still trying... but not sure i can help, since it's still confused for me...
Shouldn't row 8 be 1155 instead of 453?

M.

 

[RAJESH1960]

Still trying... but not sure i can help, since it's still confused for me...
Shouldn't row 8 be 1155 instead of 453?

M.

Yes, I forgot to mention that error. Actually it should be CBE0123 as it is a match.

Portal below Tally - but an exact match - display CBE0123

 

[Marcelo Branco]

Portal below Tally - but an exact match - display CBE0123

Exact match with different dates??? Or shouldn't we take into account the dates?

M.