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:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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
jasonb75 said:
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.
Click to expand...
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
www.mrexcel.com

Index Match Function within selected Blank Cells

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...
www.mrexcel.com www.mrexcel.com
This is the link where Marcello Branco understood what I was trying to do. I hope it helps you to understand better.
 
Upvote 0
RAJESH1960 said:
Please try deleting the data in column E and apply the above formula. You will know the mismatches.
Click to expand...
I've done that and see no difference.
RAJESH1960 said:
Which part do you want me to explain exactly.?
Click to expand...
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
jasonb75 said:
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.
Click to expand...
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
jasonb75 said:
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.
Click to expand...
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
RAJESH1960 said:
As I told you earlier that the invoice number in Books are not entered as per portal.
Click to expand...
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
jasonb75 said:
You may have mentioned it but there was a lack of clarity which you have finally provided in your latest reply.
Click to expand...
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
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
91
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
357
kevin9999
K
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
556
juanam
J
G
  • Solved
Counting zero billing days in Excel until current date
Replies
5
Views
509
awoohaw
awoohaw
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
390
felixstraube
F

Forum statistics

Threads
1,214,576
Messages
6,120,350
Members
448,956
Latest member
Adamsxl

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