Edit and correct Index, Countifs... formula.

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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
As I've asked you many times in previous threads, please include your expected results in your mini sheet.

Your task is quite a complex one and what may be obvious to you from your explanations is unlikely to be obvious to anyone else. While I do remember helping you with similar in the past, perhaps even the formula that you're using, it has been quite some time. I'm certainly not seeing enough clarity in your post to recollect what it was about.
 
Upvote 0
As I've asked you many times in previous threads, please include your expected results in your mini sheet.

Your task is quite a complex one and what may be obvious to you from your explanations is unlikely to be obvious to anyone else. While I do remember helping you with similar in the past, perhaps even the formula that you're using, it has been quite some time. I'm certainly not seeing enough clarity in your post to recollect what it was about.
Really I appreciate your concern to help Jasonb75. Which part do you want me to explain exactly.? There are 2 sheets give to me to match. One is from Portal and one is from the books. I have to match the entries which are missing in Portal and entered in books and vice versa. I could use a pivot table to do this. But, the bools sheet is not so accurate and there are a lot of mistakes, like most of the Invoice numbers are not entered in the books correctly. The amounts are also a mismatch in many cases. The dates in the books are either correct or entered on a later date. So I am trying to replace the invoice numbers in Books with the help of the formula. I know it is not that simple but not impossible also.
Please try deleting the data in column E and apply the above formula. You will know the mismatches.
 
Upvote 0
This is the link where Marcello Branco understood what I was trying to do. I hope it helps you to understand better.
 
Upvote 0
Please try deleting the data in column E and apply the above formula. You will know the mismatches.
I've done that and see no difference.
Which part do you want me to explain exactly.?
What I need to see is which rows are currently returning an incorrect result and what the result should be.

I remember last time that I looked at one of your threads it took me a while to get the idea of what you were trying to do.
 
Upvote 0
I've done that and see no difference.

What I need to see is which rows are currently returning an incorrect result and what the result should be.

I remember last time that I looked at one of your threads it took me a while to get the idea of what you were trying to do.
The colored rows in column E are all wrong. If you replace them with the right value the color will vanish. This is what I have been doing it manually. It's more than 3 hours I have doing this and I haven't completed half the data also.
 
Upvote 0
Am I right in thinking that as long as there is only one 'portal' and one 'books' for each 'no' in column f?

This formula shows no green in column E with the sample provided, but turns green if I change one of the numbers in column F so that it appears 3 times.
Excel Formula:
=IFERROR(IF(B2="PORTAL",F2,INDEX(F:F,AGGREGATE(15,6,ROW(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)
Try it on your larger data set and see what you think, if it works as needed then I'll have a look at it in more detail to see if I can trim it down a little.
 
Upvote 0
Am I right in thinking that as long as there is only one 'portal' and one 'books' for each 'no' in column f?

This formula shows no green in column E with the sample provided, but turns green if I change one of the numbers in column F so that it appears 3 times.
Excel Formula:
=IFERROR(IF(B2="PORTAL",F2,INDEX(F:F,AGGREGATE(15,6,ROW(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)
Try it on your larger data set and see what you think, if it works as needed then I'll have a look at it in more detail to see if I can trim it down a little.
The name ABC is corrected but not XYZ. As I told you earlier that the invoice number in Books are not entered as per portal. They have been entered partially or not entered at all. You have to match with the TIn, Name and value only.
 
Upvote 0
As I told you earlier that the invoice number in Books are not entered as per portal.
You may have mentioned it but there was a lack of clarity which you have finally provided in your latest reply.

Now we just need some simple clarification of the meaning of "TALLY" which appears to be absent from the sample provided. It may be in your other thread but I'm not trawling through 20-30 posts to look for it.
 
Upvote 0
You may have mentioned it but there was a lack of clarity which you have finally provided in your latest reply.
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.
 
Upvote 0

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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