RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
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.
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,"") |