Index Match Function within selected Blank Cells

RAJESH1960

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


practice 2A match 23082021.xlsx
ABCDEFGH
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)
2706PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
31807TALLY24BGZPS0000F1Z0January 46404-09-20202950.00
4832PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
51535TALLY07EUDPS1111F1ZFFebruary 275802-07-202010500.00
61120PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
72074TALLY07EUDPS1111F1ZFFebruary 303226-10-202010500.00
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
92507TALLY27AHTPJ2222N1ZNMarch Jan-1498 19-01-20218119.00
101678TALLY27AHTPJ2222N1ZNMarch 33507-08-20208819.00
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
131762TALLY27AHTPJ2222N1ZNMarch 41926-08-202015745.00
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
151747TALLY27AHTPJ2222N1ZNMarch 40424-08-202019727.00
161670TALLY27AHTPJ2222N1ZNMarch 32706-08-202019733.00
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019744.00
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
192057TALLY27AHTPJ2222N1ZNMarch 73621-10-202020300.00
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
212649TALLY27AHTPJ2222N1ZNMarch Feb-1785 22-02-202120822.00
222515TALLY27AHTPJ2222N1ZNMarch Jan-1505 20-01-202121268.00
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
252227TALLY27AHTPJ2222N1ZNMarch Dec-1102 01-12-202021275.00
262318TALLY27AHTPJ2222N1ZNMarch Dec-1235 15-12-202021322.00
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
281854TALLY29AAJFD3333B1ZRApril 51113-09-202032422.00
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
302728TALLY29AAJFD3333B1ZRApril 974820-03-202134550.00
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
321796TALLY29AAJFD3333B1ZRApril 45303-09-202037845.00
331518PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
341519PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
352733TALLY06ATLPK4444D1ZKMay 193922-03-202112222.00
362734TALLY06ATLPK4444D1ZKMay 193822-03-202112222.00
37527PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
381894TALLY06ATLPK4444D1ZKMay 55119-09-202026775.00
391896TALLY06ATLPK4444D1ZKMay 55319-09-202026775.00
402433TALLY06ATLPK4444D1ZKMay Jan-1594 05-01-202126880.00
411062PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
42809PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
432338TALLY06ATLPK4444D1ZKMay Dec-1302 17-12-202027300.00
441065PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
451066PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
461067PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
472470TALLY06ATLPK4444D1ZKMay Jan-1642 12-01-202166150.00
482471TALLY06ATLPK4444D1ZKMay Jan-1643 12-01-202166150.00
492472TALLY06ATLPK4444D1ZKMay Jan-1644 12-01-202166150.00
501563TALLY29AADCF5555H1ZUJune 15707-07-2020355.00
5116PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
532229TALLY29AADCF5555H1ZUJuly Dec-529 01-12-202021000.00
542608TALLY29AADCF5555H1ZUJuly Feb-641 09-02-202122722.00
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
572370TALLY29AADCF5555H1ZUJuly Dec-571 23-12-202025284.00
581528TALLY33AAACY6666M1Z2August 115501-07-20202950.00
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
seperate error rows
Cell Formulas
RangeFormula
E2:E59E2=IF(B2="portal",F2,"")
 
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.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
hmm... i'm confused :unsure:

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
ABCDEFGHIJ
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)Test
2706PORTAL24BGZPS0000F1Z0January141444078295014
31807TALLY24BGZPS0000F1Z0January1446444078295014
4832PORTAL07EUDPS1111F1ZFFebruary2758275844014105002758
51535TALLY07EUDPS1111F1ZFFebruary2758275844014105002758
61120PORTAL07EUDPS1111F1ZFFebruary3032303244130105003032
72074TALLY07EUDPS1111F1ZFFebruary3032303244130105003032
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-1498442158118,6S-1498
92507TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 442158119S-1498
101678TALLY27AHTPJ2222N1ZNMarch335335440508819335
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-2264405315417S-226
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-3184406915745S-318
131762TALLY27AHTPJ2222N1ZNMarchS-3184194406915745S-318
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-3034406719727S-303
151747TALLY27AHTPJ2222N1ZNMarchS-3034044406719727S-303
161670TALLY27AHTPJ2222N1ZNMarchS-2063274404919733S-206
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-2064404919744S-206
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-7364412520300S-736
192057TALLY27AHTPJ2222N1ZNMarchS-7367364412520300S-736
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-17854424920821,5S-1785
212649TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 4424920822S-1785
222515TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 4421621268S-1505
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-15054421621268,28S-1505
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-11024416521275S-1102
252227TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 4416621275Dec-1102
262318TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 4418021322Dec-1235
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-214408732422KA-2216-20-21
281854TALLY29AAJFD3333B1ZRAprilKA-2216-20-215114408732422KA-2216-20-21
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-214427534550KA-9748-20-21
302728TALLY29AAJFD3333B1ZRAprilKA-9748-20-2197484427534550KA-9748-20-21
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-214407737845KA-2035-20-21
321796TALLY29AAJFD3333B1ZRAprilKA-2035-20-214534407737845KA-2035-20-21
331518PORTAL06ATLPK4444D1ZKMay1938193844277122221938
341519PORTAL06ATLPK4444D1ZKMay1939193944277122221939
352733TALLY06ATLPK4444D1ZKMay1938193944277122221938
362734TALLY06ATLPK4444D1ZKMay1939193844277122221938
37527PORTAL06ATLPK4444D1ZKMay5825824409326775582
381894TALLY06ATLPK4444D1ZKMay5825514409326775582
391896TALLY06ATLPK4444D1ZKMay5535534409326775582
402433TALLY06ATLPK4444D1ZKMay1594Jan-1594 4420126880Jan-1594
411062PORTAL06ATLPK4444D1ZKMay1594159444204268801594
42809PORTAL06ATLPK4444D1ZKMay1302130244182273001302
432338TALLY06ATLPK4444D1ZKMay1302Dec-1302 44182273001302
441065PORTAL06ATLPK4444D1ZKMay1642164244208661501642
451066PORTAL06ATLPK4444D1ZKMay1643164344208661501643
461067PORTAL06ATLPK4444D1ZKMay1644164444208661501644
472470TALLY06ATLPK4444D1ZKMay1642Jan-1642 44208661501642
482471TALLY06ATLPK4444D1ZKMay1643Jan-1643 44208661501642
492472TALLY06ATLPK4444D1ZKMay1644Jan-1644 44208661501642
501563TALLY29AADCF5555H1ZUJune15715744019355157
5116PORTAL29AADCF5555H1ZUJune15715744020355157
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/5294416321000RI/20-21/529
532229TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 4416621000Dec-529
542608TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 4423622722Feb-641
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/6414423722722RI/20-21/641
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/5714418825284RI/20-21/571
572370TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 4418825284RI/20-21/571
581528TALLY33AAACY6666M1Z2AugustCBE012311554401329501155
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE0123440192950CBE0123
Plan11
Cell Formulas
RangeFormula
E10,E39,E26E10=F10
J2:J59J2=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
CellConditionCell FormatStop If True
J2:J59Expression=J2<>E2textNO


M.
 
Upvote 0
hmm... i'm confused :unsure:

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
ABCDEFGHIJ
1LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)Test
2706PORTAL24BGZPS0000F1Z0January141444078295014
31807TALLY24BGZPS0000F1Z0January1446444078295014
4832PORTAL07EUDPS1111F1ZFFebruary2758275844014105002758
51535TALLY07EUDPS1111F1ZFFebruary2758275844014105002758
61120PORTAL07EUDPS1111F1ZFFebruary3032303244130105003032
72074TALLY07EUDPS1111F1ZFFebruary3032303244130105003032
81073PORTAL27AHTPJ2222N1ZNMarchS-1498S-1498442158118,6S-1498
92507TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 442158119S-1498
101678TALLY27AHTPJ2222N1ZNMarch335335440508819335
11503PORTAL27AHTPJ2222N1ZNMarchS-226S-2264405315417S-226
12507PORTAL27AHTPJ2222N1ZNMarchS-318S-3184406915745S-318
131762TALLY27AHTPJ2222N1ZNMarchS-3184194406915745S-318
14506PORTAL27AHTPJ2222N1ZNMarchS-303S-3034406719727S-303
151747TALLY27AHTPJ2222N1ZNMarchS-3034044406719727S-303
161670TALLY27AHTPJ2222N1ZNMarchS-2063274404919733S-206
17501PORTAL27AHTPJ2222N1ZNMarchS-206S-2064404919744S-206
18900PORTAL27AHTPJ2222N1ZNMarchS-736S-7364412520300S-736
192057TALLY27AHTPJ2222N1ZNMarchS-7367364412520300S-736
201289PORTAL27AHTPJ2222N1ZNMarchS-1785S-17854424920821,5S-1785
212649TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 4424920822S-1785
222515TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505 4421621268S-1505
231074PORTAL27AHTPJ2222N1ZNMarchS-1505S-15054421621268,28S-1505
24759PORTAL27AHTPJ2222N1ZNMarchS-1102S-11024416521275S-1102
252227TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 4416621275Dec-1102
262318TALLY27AHTPJ2222N1ZNMarchDec-1235 Dec-1235 4418021322Dec-1235
27586PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-214408732422KA-2216-20-21
281854TALLY29AAJFD3333B1ZRAprilKA-2216-20-215114408732422KA-2216-20-21
291460PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-214427534550KA-9748-20-21
302728TALLY29AAJFD3333B1ZRAprilKA-9748-20-2197484427534550KA-9748-20-21
31583PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-214407737845KA-2035-20-21
321796TALLY29AAJFD3333B1ZRAprilKA-2035-20-214534407737845KA-2035-20-21
331518PORTAL06ATLPK4444D1ZKMay1938193844277122221938
341519PORTAL06ATLPK4444D1ZKMay1939193944277122221939
352733TALLY06ATLPK4444D1ZKMay1938193944277122221938
362734TALLY06ATLPK4444D1ZKMay1939193844277122221938
37527PORTAL06ATLPK4444D1ZKMay5825824409326775582
381894TALLY06ATLPK4444D1ZKMay5825514409326775582
391896TALLY06ATLPK4444D1ZKMay5535534409326775582
402433TALLY06ATLPK4444D1ZKMay1594Jan-1594 4420126880Jan-1594
411062PORTAL06ATLPK4444D1ZKMay1594159444204268801594
42809PORTAL06ATLPK4444D1ZKMay1302130244182273001302
432338TALLY06ATLPK4444D1ZKMay1302Dec-1302 44182273001302
441065PORTAL06ATLPK4444D1ZKMay1642164244208661501642
451066PORTAL06ATLPK4444D1ZKMay1643164344208661501643
461067PORTAL06ATLPK4444D1ZKMay1644164444208661501644
472470TALLY06ATLPK4444D1ZKMay1642Jan-1642 44208661501642
482471TALLY06ATLPK4444D1ZKMay1643Jan-1643 44208661501642
492472TALLY06ATLPK4444D1ZKMay1644Jan-1644 44208661501642
501563TALLY29AADCF5555H1ZUJune15715744019355157
5116PORTAL29AADCF5555H1ZUJune15715744020355157
52800PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/5294416321000RI/20-21/529
532229TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 4416621000Dec-529
542608TALLY29AADCF5555H1ZUJulyRI/20-21/641Feb-641 4423622722Feb-641
551430PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/6414423722722RI/20-21/641
561110PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/5714418825284RI/20-21/571
572370TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 4418825284RI/20-21/571
581528TALLY33AAACY6666M1Z2AugustCBE012311554401329501155
59417PORTAL33AAACY6666M1Z2AugustCBE0123CBE0123440192950CBE0123
Plan11
Cell Formulas
RangeFormula
E10,E39,E26E10=F10
J2:J59J2=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
CellConditionCell FormatStop If True
J2:J59Expression=J2<>E2textNO


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.
LineAs PerGSTIN of supplierTrade/Legal nameInvoice numberInvoice numberxxInvoice DateInvoice Value(₹)Remarks
1​
PORTAL24BGZPS0000F1Z0January1414
04-09-2020​
2950.00
2​
TALLY24BGZPS0000F1Z0January14464
04-09-2020​
2950.00​
3​
PORTAL07EUDPS1111F1ZFFebruary27582758
02-07-2020​
10500.00
4​
TALLY07EUDPS1111F1ZFFebruary27582758
02-07-2020​
10500.00​
5​
PORTAL07EUDPS1111F1ZFFebruary30323032
26-10-2020​
10500.00
6​
TALLY07EUDPS1111F1ZFFebruary30323032
26-10-2020​
10500.00​
7​
PORTAL27AHTPJ2222N1ZNMarchS-1498S-1498
19-01-2021​
8118.60
8​
TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498
19-01-2021​
8119.00​
9​
TALLY27AHTPJ2222N1ZNMarch335335
07-08-2020​
8819.00​
10​
PORTAL27AHTPJ2222N1ZNMarchS-226S-226
10-08-2020​
15417.00
11​
PORTAL27AHTPJ2222N1ZNMarchS-318S-318
26-08-2020​
15745.00
12​
TALLY27AHTPJ2222N1ZNMarchS-318419
26-08-2020​
15745.00​
13​
PORTAL27AHTPJ2222N1ZNMarchS-303S-303
24-08-2020​
19727.00
14​
TALLY27AHTPJ2222N1ZNMarchS-303404
24-08-2020​
19727.00​
15​
TALLY27AHTPJ2222N1ZNMarchS-206327
06-08-2020​
19733.00​
16​
PORTAL27AHTPJ2222N1ZNMarchS-206S-206
06-08-2020​
19744.00
17​
PORTAL27AHTPJ2222N1ZNMarchS-736S-736
21-10-2020​
20300.00
18​
TALLY27AHTPJ2222N1ZNMarchS-736736
21-10-2020​
20300.00​
19​
PORTAL27AHTPJ2222N1ZNMarchS-1785S-1785
22-02-2021​
20821.50
20​
TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785
22-02-2021​
20822.00​
21​
TALLY27AHTPJ2222N1ZNMarchS-1505Jan-1505
20-01-2021​
21268.00​
22​
PORTAL27AHTPJ2222N1ZNMarchS-1505S-1505
20-01-2021​
21268.28
23​
PORTAL27AHTPJ2222N1ZNMarchS-1102S-1102
30-11-2020​
21275.00
24​
TALLY27AHTPJ2222N1ZNMarchDec-1102Dec-1102
01-12-2020​
21275.00​
This is a match hence it should show S-1102
25​
TALLY27AHTPJ2222N1ZNMarchDec-1235Dec-1235
15-12-2020​
21322.00​
26​
PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-21
13-09-2020​
32422.00
27​
TALLY29AAJFD3333B1ZRAprilKA-2216-20-21511
13-09-2020​
32422.00​
28​
PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-21
20-03-2021​
34550.00
29​
TALLY29AAJFD3333B1ZRAprilKA-9748-20-219748
20-03-2021​
34550.00​
30​
PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-21
03-09-2020​
37845.00
31​
TALLY29AAJFD3333B1ZRAprilKA-2035-20-21453
03-09-2020​
37845.00​
32​
PORTAL06ATLPK4444D1ZKMay19381938
22-03-2021​
12222.00
33​
PORTAL06ATLPK4444D1ZKMay19391939
22-03-2021​
12222.00
34​
TALLY06ATLPK4444D1ZKMay19381939
22-03-2021​
12222.00​
one has to show 1938 and the other 1939
35​
TALLY06ATLPK4444D1ZKMay19381938
22-03-2021​
12222.00​
36​
PORTAL06ATLPK4444D1ZKMay582582
19-09-2020​
26775.00
37​
TALLY06ATLPK4444D1ZKMay582551
19-09-2020​
26775.00​
38​
TALLY06ATLPK4444D1ZKMay582553
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​
TALLY06ATLPK4444D1ZKMayJan-1594Jan-1594
05-01-2021​
26880.00​
40​
PORTAL06ATLPK4444D1ZKMay15941594
08-01-2021​
26880.00
41​
PORTAL06ATLPK4444D1ZKMay13021302
17-12-2020​
27300.00
42​
TALLY06ATLPK4444D1ZKMay1302Dec-1302
17-12-2020​
27300.00​
43​
PORTAL06ATLPK4444D1ZKMay16421642
12-01-2021​
66150.00
44​
PORTAL06ATLPK4444D1ZKMay16431643
12-01-2021​
66150.00
45​
PORTAL06ATLPK4444D1ZKMay16441644
12-01-2021​
66150.00
46​
TALLY06ATLPK4444D1ZKMay1642Jan-1642
12-01-2021​
66150.00​
47​
TALLY06ATLPK4444D1ZKMay1642Jan-1643
12-01-2021​
66150.00​
These are 3 different invoices of same value.
48​
TALLY06ATLPK4444D1ZKMay1642Jan-1644
12-01-2021​
66150.00​
These are 3 different invoices of same value.
49​
TALLY29AADCF5555H1ZUJune157157
07-07-2020​
355.00​
50​
PORTAL29AADCF5555H1ZUJune157157
08-07-2020​
355.00
51​
PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/529
28-11-2020​
21000.00
52​
TALLY29AADCF5555H1ZUJulyDec-529Dec-529
01-12-2020​
21000.00​
This is a match hence it should show RI/20-21/529
53​
TALLY29AADCF5555H1ZUJulyFeb-641Feb-641
09-02-2021​
22722.00​
This is a match hence it should show RI/20-21/641
54​
PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/641
10-02-2021​
22722.00
55​
PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/571
23-12-2020​
25284.00
56​
TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571
23-12-2020​
25284.00​
57​
TALLY33AAACY6666M1Z2August11551155
01-07-2020​
2950.00​
This is a match hence it should show CBE0123
58​
PORTAL33AAACY6666M1Z2AugustCBE0123CBE0123
07-07-2020​
2950.00
 
Upvote 0
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.
 
Upvote 0
To me Row 24 doesn't seem to match with Row 23 since invoice dates are different. Am i wrong?

M.
 
Upvote 0
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
ABCDEFGHI
1LineAs PerGSTIN of supplierNamecommonInvoice numberxxInvoice DateInvoice ValueErrors to correct
223PORTAL29AAJFD3333B1ZRAprilKA-2216-20-21KA-2216-20-2113-09-202032422.00
353TALLY29AAJFD3333B1ZRAprilKA-2216-20-2151113-09-202032422.00
424PORTAL29AAJFD3333B1ZRAprilKA-9748-20-21KA-9748-20-2120-03-202134550.00
554TALLY29AAJFD3333B1ZRAprilKA-9748-20-21974820-03-202134550.00
625PORTAL29AAJFD3333B1ZRAprilKA-2035-20-21KA-2035-20-2103-09-202037845.00
755TALLY29AAJFD3333B1ZRAprilKA-2035-20-2145303-09-202037845.00
830TALLY33AAACY6666M1Z2August453115501-07-20202950.00
92PORTAL33AAACY6666M1Z2AugustCBE0123CBE012307-07-20202950.00
105PORTAL07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
1134TALLY07EUDPS1111F1ZFFebruary2758275802-07-202010500.00
126PORTAL07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
1335TALLY07EUDPS1111F1ZFFebruary3032303226-10-202010500.00
143PORTAL24BGZPS0000F1Z0January141404-09-20202950.00
1531TALLY24BGZPS0000F1Z0January1446404-09-20202950.00
1615PORTAL29AADCF5555H1ZUJulyRI/20-21/529RI/20-21/52928-11-202021000.00
1743TALLY29AADCF5555H1ZUJulyRI/20-21/529Dec-529 01-12-202021000.00
1847TALLY29AADCF5555H1ZUJulyDec-529 Feb-641 09-02-202122722.00Portal below Tally - but an exact match - display RI/20-21/641
1918PORTAL29AADCF5555H1ZUJulyRI/20-21/641RI/20-21/64110-02-202122722.00
2019PORTAL29AADCF5555H1ZUJulyRI/20-21/571RI/20-21/57123-12-202025284.00
2148TALLY29AADCF5555H1ZUJulyRI/20-21/571Dec-571 23-12-202025284.00
2229TALLY29AADCF5555H1ZUJuneDec-571 15707-07-2020355.00Portal below Tally - but an exact match - display 157
231PORTAL29AADCF5555H1ZUJune15715708-07-2020355.00
244PORTAL27AHTPJ2222N1ZNMarchS-1498S-149819-01-20218118.60
2532TALLY27AHTPJ2222N1ZNMarchS-1498Jan-1498 19-01-20218119.00
2633TALLY27AHTPJ2222N1ZNMarchJan-1498 33507-08-20208819.00Not in portal - display same invoice number 335
279PORTAL27AHTPJ2222N1ZNMarchS-226S-22610-08-202015417.00
2810PORTAL27AHTPJ2222N1ZNMarchS-318S-31826-08-202015745.00
2938TALLY27AHTPJ2222N1ZNMarchS-31841926-08-202015745.00
3011PORTAL27AHTPJ2222N1ZNMarchS-303S-30324-08-202019727.00
3139TALLY27AHTPJ2222N1ZNMarchS-30340424-08-202019727.00
3212PORTAL27AHTPJ2222N1ZNMarchS-206S-20606-08-202019733.00
3340TALLY27AHTPJ2222N1ZNMarchS-20632706-08-202019733.00
3413PORTAL27AHTPJ2222N1ZNMarchS-736S-73621-10-202020300.00
3541TALLY27AHTPJ2222N1ZNMarchS-73673621-10-202020300.00
3614PORTAL27AHTPJ2222N1ZNMarchS-1785S-178522-02-202120821.50
3742TALLY27AHTPJ2222N1ZNMarchS-1785Feb-1785 22-02-202120822.00
3844TALLY27AHTPJ2222N1ZNMarchFeb-1785 Jan-1505 20-01-202121268.00Portal below Tally - but an exact match - display S-1505
3916PORTAL27AHTPJ2222N1ZNMarchS-1505S-150520-01-202121268.28
4017PORTAL27AHTPJ2222N1ZNMarchS-1102S-110230-11-202021275.00
4145TALLY27AHTPJ2222N1ZNMarchS-1102Dec-1102 01-12-202021275.00
4246TALLY27AHTPJ2222N1ZNMarchDec-1102 Dec-1235 15-12-202021322.00Not in portal - display same invoice number Dec-1235
437PORTAL06ATLPK4444D1ZKMay1938193822-03-202112222.00
448PORTAL06ATLPK4444D1ZKMay1939193922-03-202112222.00
4536TALLY06ATLPK4444D1ZKMay1939193922-03-202112222.00
4637TALLY06ATLPK4444D1ZKMay1939193822-03-202112222.00same value invoices with different invoice number - display 1938
4720PORTAL06ATLPK4444D1ZKMay58258219-09-202026775.00
4849TALLY06ATLPK4444D1ZKMay58255119-09-202026775.00
4950TALLY06ATLPK4444D1ZKMay55155319-09-202026775.00
5051TALLY06ATLPK4444D1ZKMay553Jan-1594 05-01-202126880.00
5121PORTAL06ATLPK4444D1ZKMay1594159408-01-202126880.00
5222PORTAL06ATLPK4444D1ZKMay1302130217-12-202027300.00
5352TALLY06ATLPK4444D1ZKMay1302Dec-1302 17-12-202027300.00
5426PORTAL06ATLPK4444D1ZKMay1642164212-01-202166150.00
5527PORTAL06ATLPK4444D1ZKMay1643164312-01-202166150.00
5628PORTAL06ATLPK4444D1ZKMay1644164412-01-202166150.00
5756TALLY06ATLPK4444D1ZKMay1644Jan-1642 12-01-202166150.00same value invoices with different invoice number - display 1642
5857TALLY06ATLPK4444D1ZKMayJan-1642 Jan-1643 12-01-202166150.00same value invoices with different invoice number - display 1643
5958TALLY06ATLPK4444D1ZKMayJan-1643 Jan-1644 12-01-202166150.00same value invoices with different invoice number - display 1644
Mr.Excel
Cell Formulas
RangeFormula
E2:E59E2=IF(B2="PORTAL",F2,IF(COUNTIFS(B:B,"PORTAL",D:D,D2,C:C,C2),F1,F2))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top