Edit and correct Index, Countifs... formula.

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello Guys

I need help in making this formula more accurate. As the date has more than 20000 rows, this formula has helped me to reduce my work and save 90% of my time. Wrongly posted number in books is also corrected by the formula. Next, I have to spend time to check the mismatches of each entry. So, I have formatted the sheet with a few CF in various columns to make it easier to check. If, the formula is rightly edited where ever required, it will take the value of Portal more than once, then it would clear more than 80% of my time to check.

The Formula Used in column E to get the Portal No. in Tally as per value is '=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$20000,AGGREGATE(15,6,(ROW(F$2:F$20000)-ROW(F$2)+1)/((B$2:B$20000="PORTAL")*(C$2:C$20000=C2)*(H$2:H$20000>=H2-1)*(H$2:H$20000<=H2+1)),MAX(1,COUNTIFS(B$2:B2,"TALLY",C$2:C2,C2,H$2:H2,">="&H2-1,H$2:H2,"<="&H2+1))))),F2)y

The formula takes one number of Portal only when the values are same. I have formatted the E column to check whether there are any mistakes. Column H is formatted to check multiple same amounts as per name.

Anyone’s help will be very helpful.
Please note that the dates as per portal and as per books in some cases may not be the same. That is a reason I haven't taken the date column in the formula.

Book1
ABCDEFGH
1LineAs perTINNAMECOMMON NO.NO.DATEVALUE
221PORTAL39XHHPH4528G1XXABC3256325608-02-2021368.76
338BOOKS39XHHPH4528G1XXABC3256325708-02-2021368.76
419PORTAL39XHHPH4528G1XXABC2973297318-01-2021525.00
552BOOKS39XHHPH4528G1XXABC2973297318-01-2021525.00
628PORTAL39XHHPH4528G1XXABC3900390025-03-2021637.20
759BOOKS39XHHPH4528G1XXABC3900390025-03-2021637.20
860BOOKS39XHHPH4528G1XXABC3900391226-03-2021637.20
929PORTAL39XHHPH4528G1XXABC3912391226-03-2021637.20
1030BOOKS39XHHPH4528G1XXABC818119-05-20201829.00
112PORTAL39XHHPH4528G1XXABC56056001-07-20201837.50
1241BOOKS39XHHPH4528G1XXABC56056001-07-20201837.50
1346BOOKS39XHHPH4528G1XXABC560175508-10-20201837.50
149PORTAL39XHHPH4528G1XXABC1755175508-10-20201837.50
1515PORTAL39XHHPH4528G1XXABC2290229021-11-20201968.76
1649BOOKS39XHHPH4528G1XXABC2290229021-11-20201968.76
171PORTAL39XHHPH4528G1XXABC23323301-06-20201995.00
1840BOOKS39XHHPH4528G1XXABC23323301-06-20201995.00
1948BOOKS39XHHPH4528G1XXABC233221013-11-20201995.00
2014PORTAL39XHHPH4528G1XXABC2210221013-11-20201995.00
214PORTAL39XHHPH4528G1XXABC74474418-07-20202768.76
2233BOOKS39XHHPH4528G1XXABC74474418-07-20202768.76
238PORTAL39XHHPH4528G1XXABC1725172506-10-20202992.50
2445BOOKS39XHHPH4528G1XXABC1725172506-10-20202992.50
2550BOOKS39XHHPH4528G1XXABC1725245004-12-20202992.50
2651BOOKS39XHHPH4528G1XXABC1725267724-12-20202992.50
2755BOOKS39XHHPH4528G1XXABC1725338717-02-20212992.50
2856BOOKS39XHHPH4528G1XXABC1725341919-02-20212992.50
2917PORTAL39XHHPH4528G1XXABC2450245004-12-20202992.50
3018PORTAL39XHHPH4528G1XXABC2677267724-12-20202992.50
3123PORTAL39XHHPH4528G1XXABC3386338617-02-20212992.50
3224PORTAL39XHHPH4528G1XXABC3418341819-02-20212992.50
3331BOOKS39XHHPH4528G1XXABC21321329-05-20205347.18
346PORTAL39XHHPH4528G1XXABC98898806-08-20205460.00
3543BOOKS39XHHPH4528G1XXABC98898806-08-20205460.00
3647BOOKS39XHHPH4528G1XXABC988186616-10-20205460.00
3757BOOKS39XHHPH4528G1XXABC988348225-02-20215460.00
3810PORTAL39XHHPH4528G1XXABC1866186616-10-20205460.00
3925PORTAL39XHHPH4528G1XXABC3481348125-02-20215460.00
4026PORTAL39XHHPH4528G1XXABC3518351827-02-20218850.00
4139BOOKS39XHHPH4528G1XXABC3518351927-02-20218850.00
4236BOOKS39XHHPH4528G1XXABC2122212207-11-20209180.00
4313PORTAL39XHHPH4528G1XXABC2122212207-11-20209180.40
4437BOOKS39XHHPH4528G1XXABC2122244204-12-20209180.40
4516PORTAL39XHHPH4528G1XXABC2442244204-12-20209180.40
4611PORTAL39XHHPH4528G1XXABC1966196624-10-202010164.76
4734BOOKS39XHHPH4528G1XXABC1966196624-10-202010164.76
485PORTAL39XHHPH4528G1XXABC95095004-08-202010500.00
4942BOOKS39XHHPH4528G1XXABC95095004-08-202010500.00
5044BOOKS39XHHPH4528G1XXABC950148714-09-202010500.00
5153BOOKS39XHHPH4528G1XXABC950304722-01-202110500.00
5254BOOKS39XHHPH4528G1XXABC950329310-02-202110500.00
5358BOOKS39XHHPH4528G1XXABC950380619-03-202110500.00
547PORTAL39XHHPH4528G1XXABC1487148714-09-202010500.00
5520PORTAL39XHHPH4528G1XXABC3047304722-01-202110500.00
5622PORTAL39XHHPH4528G1XXABC3292329210-02-202110500.00
5727PORTAL39XHHPH4528G1XXABC3806380619-03-202110500.00
583PORTAL39XHHPH4528G1XXABC57657602-07-202010502.00
5932BOOKS39XHHPH4528G1XXABC57657602-07-202010502.00
6035BOOKS39XHHPH4528G1XXABC576201931-10-202010502.00
6112PORTAL39XHHPH4528G1XXABC2019201931-10-202010502.00
6263PORTAL39XDKPN0022Q1XTXYZXYZ3921XYZ392115-07-20208408.00
6385BOOKS39XDKPN0022Q1XTXYZXYZ3921392115-07-20208408.00
6461PORTAL39XDKPN0022Q1XTXYZXYZ3917XYZ391713-07-20208850.00
6584BOOKS39XDKPN0022Q1XTXYZXYZ3917391713-07-20208850.00
6686BOOKS39XDKPN0022Q1XTXYZXYZ3917404505-01-20218850.00
6787BOOKS39XDKPN0022Q1XTXYZXYZ3917388711-05-20208850.00
6875PORTAL39XDKPN0022Q1XTXYZXYZ4045XYZ404505-01-20218850.00
6970PORTAL39XDKPN0022Q1XTXYZXYZ4006XYZ400627-11-202022420.00
7097BOOKS39XDKPN0022Q1XTXYZXYZ4006400627-11-202022420.00
7165PORTAL39XDKPN0022Q1XTXYZXYZ3949XYZ394901-09-202025960.00
7292BOOKS39XDKPN0022Q1XTXYZXYZ3949394901-09-202025960.00
73101BOOKS39XDKPN0022Q1XTXYZXYZ3949404404-01-202125960.00
7474PORTAL39XDKPN0022Q1XTXYZXYZ4044XYZ404404-01-202125960.00
7569PORTAL39XDKPN0022Q1XTXYZXYZ3995XYZ399523-10-202043188.00
7696BOOKS39XDKPN0022Q1XTXYZXYZ3995399523-10-202043188.00
7767PORTAL39XDKPN0022Q1XTXYZXYZ3970XYZ397030-09-202051920.00
7894BOOKS39XDKPN0022Q1XTXYZXYZ3970397001-10-202051920.00
7999BOOKS39XDKPN0022Q1XTXYZXYZ3970402722-12-202051920.00
80100BOOKS39XDKPN0022Q1XTXYZXYZ3970403525-12-202051920.00
81102BOOKS39XDKPN0022Q1XTXYZXYZ3970404909-01-202151920.00
8272PORTAL39XDKPN0022Q1XTXYZXYZ4027XYZ402722-12-202051920.00
8373PORTAL39XDKPN0022Q1XTXYZXYZ4035XYZ403525-12-202051920.00
8476PORTAL39XDKPN0022Q1XTXYZXYZ4049XYZ404909-01-202151920.00
8568PORTAL39XDKPN0022Q1XTXYZXYZ3976XYZ397613-10-202077880.00
8695BOOKS39XDKPN0022Q1XTXYZXYZ3976397613-10-202077880.00
87103BOOKS39XDKPN0022Q1XTXYZXYZ3976405013-01-202177880.00
88104BOOKS39XDKPN0022Q1XTXYZXYZ3976406604-02-202177880.00
89105BOOKS39XDKPN0022Q1XTXYZXYZ3976407412-02-202177880.00
9077PORTAL39XDKPN0022Q1XTXYZXYZ4050XYZ405013-01-202177880.00
9178PORTAL39XDKPN0022Q1XTXYZXYZ4066XYZ406604-02-202177880.00
9279PORTAL39XDKPN0022Q1XTXYZXYZ4074XYZ407412-02-202177880.00
9383PORTAL39XDKPN0022Q1XTXYZXYZ4111XYZ411131-03-202183780.00
94109BOOKS39XDKPN0022Q1XTXYZXYZ4111411131-03-202183780.00
9566PORTAL39XDKPN0022Q1XTXYZXYZ3951XYZ395104-09-2020103840.00
9693BOOKS39XDKPN0022Q1XTXYZXYZ3951395104-09-2020103840.00
9762PORTAL39XDKPN0022Q1XTXYZXYZ3918XYZ391814-07-2020129800.00
9888BOOKS39XDKPN0022Q1XTXYZXYZ3918388611-05-2020129800.00
9989BOOKS39XDKPN0022Q1XTXYZXYZ3918388916-05-2020129800.00
10090BOOKS39XDKPN0022Q1XTXYZXYZ3918391814-07-2020129800.00
10191BOOKS39XDKPN0022Q1XTXYZXYZ3918393404-08-2020129800.00
10298BOOKS39XDKPN0022Q1XTXYZXYZ3918402415-12-2020129800.00
103106BOOKS39XDKPN0022Q1XTXYZXYZ3918408601-03-2021129800.00
104107BOOKS39XDKPN0022Q1XTXYZXYZ3918409308-03-2021129800.00
105108BOOKS39XDKPN0022Q1XTXYZXYZ3918410015-03-2021129800.00
10664PORTAL39XDKPN0022Q1XTXYZXYZ3934XYZ393404-08-2020129800.00
10771PORTAL39XDKPN0022Q1XTXYZXYZ4024XYZ402415-12-2020129800.00
10880PORTAL39XDKPN0022Q1XTXYZXYZ4086XYZ408627-02-2021129800.00
10981PORTAL39XDKPN0022Q1XTXYZXYZ4093XYZ409308-03-2021129800.00
11082PORTAL39XDKPN0022Q1XTXYZXYZ4100XYZ410015-03-2021129800.00
Portal + Tally
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E110Expression=COUNTIFS(D$2:D$20000,D2,E$2:E$20000,E2)>2textNO
H2:I110Expression=COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3textNO
 
Last edited:
Sort by date Sort by votes
RAJESH1960 said:
Query Pending to match Invoice NO..xlsx
BCDEFGH
62PORTAL39XDKPN0022Q1XTXYZXYZ3921XYZ392115-07-20208408.00
63BOOKS39XDKPN0022Q1XTXYZ3921392115-07-20208408.00
64PORTAL39XDKPN0022Q1XTXYZXYZ3917XYZ391713-07-20208850.00
65BOOKS39XDKPN0022Q1XTXYZ3917391713-07-20208850.00
66BOOKS39XDKPN0022Q1XTXYZ4045404505-01-20218850.00
67BOOKS39XDKPN0022Q1XTXYZ3887388711-05-20208850.00
68PORTAL39XDKPN0022Q1XTXYZXYZ4045XYZ404505-01-20218850.00
69PORTAL39XDKPN0022Q1XTXYZXYZ4006XYZ400627-11-202022420.00
70BOOKS39XDKPN0022Q1XTXYZ4006400627-11-202022420.00
71PORTAL39XDKPN0022Q1XTXYZXYZ3949XYZ394901-09-202025960.00
72BOOKS39XDKPN0022Q1XTXYZ3949394901-09-202025960.00
73BOOKS39XDKPN0022Q1XTXYZ4044404404-01-202125960.00
74PORTAL39XDKPN0022Q1XTXYZXYZ4044XYZ404404-01-202125960.00
Check misMatch Invoice NO.
Cell Formulas
RangeFormula
E62:E74E62=IFERROR(IF(B62="PORTAL",F62,AGGREGATE(15,6,F$2:F$2000/(B$2:B$20000="PORTAL")/(C$2:C$20000=C62)/(H$2:H$20000>=H62-1)/(H$2:H$20000<=H62+1)/(F$2:F$2000=F62),MAX(1,COUNTIFS(B$2:B62,"TALLY",C$2:C62,C62,H$2:H62,">="&H62-1,H$2:H62,"<="&H62+1)))),F62)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E110Expression=COUNTIFS(D$2:D$20000,D2,E$2:E$20000,E2)>2textNO
H2:I61,H63:I110,H62Expression=COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3textNO

This is the result from your formula.
Click to expand...
and this is the actual result I should get. I have entered it manually.
Query Pending to match Invoice NO..xlsx
BCDEFGH
62PORTAL39XDKPN0022Q1XTXYZXYZ3921XYZ392115-07-20208408.00
63BOOKS39XDKPN0022Q1XTXYZXYZ3921392115-07-20208408.00
64PORTAL39XDKPN0022Q1XTXYZXYZ3917XYZ391713-07-20208850.00
65BOOKS39XDKPN0022Q1XTXYZXYZ3917391713-07-20208850.00
66BOOKS39XDKPN0022Q1XTXYZXYZ4045404505-01-20218850.00
67BOOKS39XDKPN0022Q1XTXYZ3887388711-05-20208850.00
68PORTAL39XDKPN0022Q1XTXYZXYZ4045XYZ404505-01-20218850.00
69PORTAL39XDKPN0022Q1XTXYZXYZ4006XYZ400627-11-202022420.00
70BOOKS39XDKPN0022Q1XTXYZXYZ4006400627-11-202022420.00
71PORTAL39XDKPN0022Q1XTXYZXYZ3949XYZ394901-09-202025960.00
72BOOKS39XDKPN0022Q1XTXYZXYZ3949394901-09-202025960.00
73BOOKS39XDKPN0022Q1XTXYZXYZ4044404404-01-202125960.00
74PORTAL39XDKPN0022Q1XTXYZXYZ4044XYZ404404-01-202125960.00
Check misMatch Invoice NO.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E110Expression=COUNTIFS(D$2:D$20000,D2,E$2:E$20000,E2)>2textNO
H2:I61,H63:I110,H62Expression=COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3textNO
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
RAJESH1960 said:
This is the result from your formula.
Click to expand...
I've already seen those results from my own testing, that contains no useful information.

edit:- You could have added both mini sheets to one post, or better still entered the manual results in another column of the first one for simplicity.

I had a feeling that it was going to be a mistake when I posted the first reply, I'm unsubscribing from this thread before my patience runs out. Hopefully somebody else will give it a go for you.
 
Upvote 0
RAJESH1960 said:
Query Pending to match Invoice NO..xlsx
BCDEFGH
62PORTAL39XDKPN0022Q1XTXYZXYZ3921XYZ392115-07-20208408.00
63BOOKS39XDKPN0022Q1XTXYZ3921392115-07-20208408.00
64PORTAL39XDKPN0022Q1XTXYZXYZ3917XYZ391713-07-20208850.00
65BOOKS39XDKPN0022Q1XTXYZ3917391713-07-20208850.00
66BOOKS39XDKPN0022Q1XTXYZ4045404505-01-20218850.00
67BOOKS39XDKPN0022Q1XTXYZ3887388711-05-20208850.00
68PORTAL39XDKPN0022Q1XTXYZXYZ4045XYZ404505-01-20218850.00
69PORTAL39XDKPN0022Q1XTXYZXYZ4006XYZ400627-11-202022420.00
70BOOKS39XDKPN0022Q1XTXYZ4006400627-11-202022420.00
71PORTAL39XDKPN0022Q1XTXYZXYZ3949XYZ394901-09-202025960.00
72BOOKS39XDKPN0022Q1XTXYZ3949394901-09-202025960.00
73BOOKS39XDKPN0022Q1XTXYZ4044404404-01-202125960.00
74PORTAL39XDKPN0022Q1XTXYZXYZ4044XYZ404404-01-202125960.00
Check misMatch Invoice NO.
Cell Formulas
RangeFormula
E62:E74E62=IFERROR(IF(B62="PORTAL",F62,AGGREGATE(15,6,F$2:F$2000/(B$2:B$20000="PORTAL")/(C$2:C$20000=C62)/(H$2:H$20000>=H62-1)/(H$2:H$20000<=H62+1)/(F$2:F$2000=F62),MAX(1,COUNTIFS(B$2:B62,"TALLY",C$2:C62,C62,H$2:H62,">="&H62-1,H$2:H62,"<="&H62+1)))),F62)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E110Expression=COUNTIFS(D$2:D$20000,D2,E$2:E$20000,E2)>2textNO
H2:I61,H63:I110,H62Expression=COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3textNO

This is the result from your formula.
Click to expand...
It is from the formula you shared, the Ist image and the 2nd image I did it manually to correct it.
 
Upvote 0
jasonb75 said:
I've already seen those results from my own testing, that contains no useful information.

edit:- You could have added both mini sheets to one post, or better still entered the manual results in another column of the first one for simplicity.

I had a feeling that it was going to be a mistake when I posted the first reply, I'm unsubscribing from this thread before my patience runs out. Hopefully somebody else will give it a go for you.
Click to expand...
Sorry JasonB. I was not able to make you understand fully. Thanks for your time.
 
Upvote 0
Referring back to the very beginning of this thread, more specifically, your first words in post 1
RAJESH1960 said:
Hello Guys

I need help in making this formula more accurate.
Click to expand...
It appears to be you that needs to be more accurate with what you are doing. I just looked at your previous thread, it appears that the reason for @Marcelo Branco's formula not working is because you have changed 'TALLY' to 'BOOKS' in your table but left the formula looking for 'TALLY' records. When the formula and table both say the same thing it works correctly.

What you have done is the excel equivalent to changing a lock and still trying to use the old key, it's never going to work!
 
Upvote 0
Query Pending to match Invoice NO..xlsx
ABCDEFGHIJ
1LineAs perTINNAMEMarcelloNO.DATEVALUEJaonbRequired Result
221PORTAL39XHHPH4528G1XXABC3256325608-02-2021368.7632563256
338TALLY39XHHPH4528G1XXABC3256325708-02-2021368.7632573256
419PORTAL39XHHPH4528G1XXABC2973297318-01-2021525.0029732973
552TALLY39XHHPH4528G1XXABC2973297318-01-2021525.0029732973
628PORTAL39XHHPH4528G1XXABC3900390025-03-2021637.2039003900
729PORTAL39XHHPH4528G1XXABC3912391226-03-2021637.2039123912
859TALLY39XHHPH4528G1XXABC3900390025-03-2021637.2039003900
960TALLY39XHHPH4528G1XXABC3912391226-03-2021637.2039123912
1030TALLY39XHHPH4528G1XXABC818119-05-20201829.008181
112PORTAL39XHHPH4528G1XXABC56056001-07-20201837.50560560
129PORTAL39XHHPH4528G1XXABC1755175508-10-20201837.5017551755
1341TALLY39XHHPH4528G1XXABC56056001-07-20201837.50560560
1446TALLY39XHHPH4528G1XXABC1755175508-10-20201837.5017551755
1515PORTAL39XHHPH4528G1XXABC2290229021-11-20201968.7622902290
1649TALLY39XHHPH4528G1XXABC2290229021-11-20201968.7622902290
171PORTAL39XHHPH4528G1XXABC23323301-06-20201995.00233233
1814PORTAL39XHHPH4528G1XXABC2210221013-11-20201995.0022102210
1940TALLY39XHHPH4528G1XXABC23323301-06-20201995.00233233
2048TALLY39XHHPH4528G1XXABC2210221013-11-20201995.0022102210
214PORTAL39XHHPH4528G1XXABC74474418-07-20202768.76744744
2233TALLY39XHHPH4528G1XXABC74474418-07-20202768.76744744
238PORTAL39XHHPH4528G1XXABC1725172506-10-20202992.5017251725
2417PORTAL39XHHPH4528G1XXABC2450245004-12-20202992.5024502450
2518PORTAL39XHHPH4528G1XXABC2677267724-12-20202992.5026772677
2623PORTAL39XHHPH4528G1XXABC3386338617-02-20212992.5033863386
2724PORTAL39XHHPH4528G1XXABC3418341819-02-20212992.5034183418
2845TALLY39XHHPH4528G1XXABC1725172506-10-20202992.5017251725
2950TALLY39XHHPH4528G1XXABC2450245004-12-20202992.5024502450
3051TALLY39XHHPH4528G1XXABC2677267724-12-20202992.5026772677
3155TALLY39XHHPH4528G1XXABC3386338717-02-20212992.5033873386
3256TALLY39XHHPH4528G1XXABC3418341919-02-20212992.5034193418
3331TALLY39XHHPH4528G1XXABC21321329-05-20205347.18213213
346PORTAL39XHHPH4528G1XXABC98898806-08-20205460.00988988
3510PORTAL39XHHPH4528G1XXABC1866186616-10-20205460.0018661866
3625PORTAL39XHHPH4528G1XXABC3481348125-02-20215460.0034813481
3743TALLY39XHHPH4528G1XXABC98898806-08-20205460.00988988
3847TALLY39XHHPH4528G1XXABC1866186616-10-20205460.0018661866
3957TALLY39XHHPH4528G1XXABC3481348225-02-20215460.0034823481
4026PORTAL39XHHPH4528G1XXABC3518351827-02-20218850.0035183518
4139TALLY39XHHPH4528G1XXABC3518351927-02-20218850.0035193518
4236TALLY39XHHPH4528G1XXABC2122212207-11-20209180.0021222122
4313PORTAL39XHHPH4528G1XXABC2122212207-11-20209180.4021222122
4416PORTAL39XHHPH4528G1XXABC2442244204-12-20209180.4024422442
4537TALLY39XHHPH4528G1XXABC2442244204-12-20209180.4024422442
4611PORTAL39XHHPH4528G1XXABC1966196624-10-202010164.7619661966
4734TALLY39XHHPH4528G1XXABC1966196624-10-202010164.7619661966
485PORTAL39XHHPH4528G1XXABC95095004-08-202010500.00950950
497PORTAL39XHHPH4528G1XXABC1487148714-09-202010500.0014871487
5020PORTAL39XHHPH4528G1XXABC3047304722-01-202110500.0030473047
5122PORTAL39XHHPH4528G1XXABC3292329210-02-202110500.0032923292
5227PORTAL39XHHPH4528G1XXABC3806380619-03-202110500.0038063806
5342TALLY39XHHPH4528G1XXABC95095004-08-202010500.00950950
5444TALLY39XHHPH4528G1XXABC1487148714-09-202010500.0014871487
5553TALLY39XHHPH4528G1XXABC3047304722-01-202110500.0030473047
5654TALLY39XHHPH4528G1XXABC3292329310-02-202110500.0032933292
5758TALLY39XHHPH4528G1XXABC3806380619-03-202110500.0038063806
583PORTAL39XHHPH4528G1XXABC57657602-07-202010502.00576576
5912PORTAL39XHHPH4528G1XXABC2019201931-10-202010502.0020192019
6032TALLY39XHHPH4528G1XXABC57657602-07-202010502.00576576
6135TALLY39XHHPH4528G1XXABC2019201931-10-202010502.0020192019
6263TALLY39XDKPN0022Q1XTXYZXYZ3921XYZ392115-07-20208408.00XYZ3921XYZ3921
6385TALLY39XDKPN0022Q1XTXYZ3921392115-07-20208408.003921XYZ3921
6461TALLY39XDKPN0022Q1XTXYZXYZ3917XYZ391713-07-20208850.00XYZ3917XYZ3917
6575TALLY39XDKPN0022Q1XTXYZXYZ4045XYZ404505-01-20218850.00XYZ4045XYZ4045
6684TALLY39XDKPN0022Q1XTXYZ3917391713-07-20208850.003917XYZ3917
6786TALLY39XDKPN0022Q1XTXYZ4045404505-01-20218850.004045XYZ4045
6887TALLY39XDKPN0022Q1XTXYZ3887388711-05-20208850.0038873887
6970TALLY39XDKPN0022Q1XTXYZXYZ4006XYZ400627-11-202022420.00XYZ4006XYZ4006
7097TALLY39XDKPN0022Q1XTXYZ4006400627-11-202022420.004006XYZ4006
7165TALLY39XDKPN0022Q1XTXYZXYZ3949XYZ394901-09-202025960.00XYZ3949XYZ3949
7274TALLY39XDKPN0022Q1XTXYZXYZ4044XYZ404404-01-202125960.00XYZ4044XYZ4044
7392TALLY39XDKPN0022Q1XTXYZ3949394901-09-202025960.003949XYZ3949
74101TALLY39XDKPN0022Q1XTXYZ4044404404-01-202125960.004044XYZ4044
7569TALLY39XDKPN0022Q1XTXYZXYZ3995XYZ399523-10-202043188.00XYZ3995XYZ3995
7696TALLY39XDKPN0022Q1XTXYZ3995399523-10-202043188.003995XYZ3995
7767TALLY39XDKPN0022Q1XTXYZXYZ3970XYZ397030-09-202051920.00XYZ3970XYZ3970
7872TALLY39XDKPN0022Q1XTXYZXYZ4027XYZ402722-12-202051920.00XYZ4027XYZ4027
7973TALLY39XDKPN0022Q1XTXYZXYZ4035XYZ403525-12-202051920.00XYZ4035XYZ4035
8076TALLY39XDKPN0022Q1XTXYZXYZ4049XYZ404909-01-202151920.00XYZ4049XYZ4049
8194TALLY39XDKPN0022Q1XTXYZ3970397001-10-202051920.003970XYZ3970
8299TALLY39XDKPN0022Q1XTXYZ4027402722-12-202051920.004027XYZ4027
83100TALLY39XDKPN0022Q1XTXYZ4035403525-12-202051920.004035XYZ4035
84102TALLY39XDKPN0022Q1XTXYZ4049404909-01-202151920.004049XYZ4049
8568TALLY39XDKPN0022Q1XTXYZXYZ3976XYZ397613-10-202077880.00XYZ3976XYZ3976
8677TALLY39XDKPN0022Q1XTXYZXYZ4050XYZ405013-01-202177880.00XYZ4050XYZ4050
8778TALLY39XDKPN0022Q1XTXYZXYZ4066XYZ406604-02-202177880.00XYZ4066XYZ4066
8879TALLY39XDKPN0022Q1XTXYZXYZ4074XYZ407412-02-202177880.00XYZ4074XYZ4074
8995TALLY39XDKPN0022Q1XTXYZ3976397613-10-202077880.003976XYZ3976
90103TALLY39XDKPN0022Q1XTXYZ4050405013-01-202177880.004050XYZ4050
91104TALLY39XDKPN0022Q1XTXYZ4066406604-02-202177880.004066XYZ4066
92105TALLY39XDKPN0022Q1XTXYZ4074407412-02-202177880.004074XYZ4074
9383TALLY39XDKPN0022Q1XTXYZXYZ4111XYZ411131-03-202183780.00XYZ4111XYZ4111
94109TALLY39XDKPN0022Q1XTXYZ4111411131-03-202183780.004111XYZ4111
9566TALLY39XDKPN0022Q1XTXYZXYZ3951XYZ395104-09-2020103840.00XYZ3951XYZ3951
9693TALLY39XDKPN0022Q1XTXYZ3951395104-09-2020103840.003951XYZ3951
9762TALLY39XDKPN0022Q1XTXYZXYZ3918XYZ391814-07-2020129800.00XYZ3918XYZ3918
9864TALLY39XDKPN0022Q1XTXYZXYZ3934XYZ393404-08-2020129800.00XYZ3934XYZ3934
9971TALLY39XDKPN0022Q1XTXYZXYZ4024XYZ402415-12-2020129800.00XYZ4024XYZ4024
10080TALLY39XDKPN0022Q1XTXYZXYZ4086XYZ408627-02-2021129800.00XYZ4086XYZ4086
10181TALLY39XDKPN0022Q1XTXYZXYZ4093XYZ409308-03-2021129800.00XYZ4093XYZ4093
10282TALLY39XDKPN0022Q1XTXYZXYZ4100XYZ410015-03-2021129800.00XYZ4100XYZ4100
10388TALLY39XDKPN0022Q1XTXYZ3886388611-05-2020129800.0038863886
10489TALLY39XDKPN0022Q1XTXYZ3889388916-05-2020129800.0038893889
10590TALLY39XDKPN0022Q1XTXYZ3918391814-07-2020129800.003918XYZ3918
10691TALLY39XDKPN0022Q1XTXYZ3934393404-08-2020129800.003934XYZ3934
10798TALLY39XDKPN0022Q1XTXYZ4024402415-12-2020129800.004024XYZ4024
108106TALLY39XDKPN0022Q1XTXYZ4086408601-03-2021129800.004086XYZ4086
109107TALLY39XDKPN0022Q1XTXYZ4093409308-03-2021129800.004093XYZ4093
110108TALLY39XDKPN0022Q1XTXYZ4100410015-03-2021129800.004100XYZ4100
Check misMatch Invoice NO.
Cell Formulas
RangeFormula
E2:E110E2=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$20000,AGGREGATE(15,6,(ROW(F$2:F$20000)-ROW(F$2)+1)/((B$2:B$20000="PORTAL")*(C$2:C$20000=C2)*(H$2:H$20000>=H2-1)*(H$2:H$20000<=H2+1)),MAX(1,COUNTIFS(B$2:B2,"TALLY",C$2:C2,C2,H$2:H2,">="&H2-1,H$2:H2,"<="&H2+1))))),F2)
I2:I110I2=IFERROR(IF(B2="PORTAL",F2,AGGREGATE(15,6,F$2:F$2000/(B$2:B$20000="PORTAL")/(C$2:C$20000=C2)/(H$2:H$20000>=H2-1)/(H$2:H$20000<=H2+1)/(F$2:F$2000=F2),MAX(1,COUNTIFS(B$2:B2,"TALLY",C$2:C2,C2,H$2:H2,">="&H2-1,H$2:H2,"<="&H2+1)))),F2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J2:K61,K63,J64:K110Expression=COUNTIFS(I$2:I$19705,I2,J$2:J$19705,J2)>3textNO
H2:H110Expression=COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3textNO
I2:I110Expression=COUNTIFS(D$2:D$20000,D2,I$2:I$20000,I2)>2textNO
 
Upvote 0
The results in column I are from Marcelo's earlier solution, not anything that I have provided. This is the position that you were in when you started this thread and you have finally provided the information that should have been included in post 1. Congratulations, it only took you 17 attempts to get there. Better late than never I suppose.

Hopefully @Marcelo Branco will be able to provide you with additional support. I'm going to busy giving piano lessons to a goldifsh.
 
Upvote 0
jasonb75 said:
The results in column I are from Marcelo's earlier solution, not anything that I have provided. This is the position that you were in when you started this thread and you have finally provided the information that should have been included in post 1. Congratulations, it only took you 17 attempts to get there. Better late than never I suppose.
Click to expand...
? I am going mad trying to edit this formula. Every month I get different data to match I have a lot of manual work after applying Marcelo's formula. His formula has at least helped me to finish half of my work in Less than a minute. The rest I have been trying to explain what needs to be done. I just completed the match yesterday but again next month I will have to try again with a different data I may receive. I just don't like to give up even if it is a difficult task.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
231
Raj1979
R
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
128
RobP
R
V
  • Question Question
US/European Date Format Updates Not Working on Macro Run
Replies
0
Views
63
vancher19
V
S
  • Solved
Count of active employees between dates.
Replies
3
Views
634
Fluff
Fluff

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

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