RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- 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.
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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Line | As per | TIN | NAME | COMMON NO. | NO. | DATE | VALUE | ||
2 | 21 | PORTAL | 39XHHPH4528G1XX | ABC | 3256 | 3256 | 08-02-2021 | 368.76 | ||
3 | 38 | BOOKS | 39XHHPH4528G1XX | ABC | 3256 | 3257 | 08-02-2021 | 368.76 | ||
4 | 19 | PORTAL | 39XHHPH4528G1XX | ABC | 2973 | 2973 | 18-01-2021 | 525.00 | ||
5 | 52 | BOOKS | 39XHHPH4528G1XX | ABC | 2973 | 2973 | 18-01-2021 | 525.00 | ||
6 | 28 | PORTAL | 39XHHPH4528G1XX | ABC | 3900 | 3900 | 25-03-2021 | 637.20 | ||
7 | 59 | BOOKS | 39XHHPH4528G1XX | ABC | 3900 | 3900 | 25-03-2021 | 637.20 | ||
8 | 60 | BOOKS | 39XHHPH4528G1XX | ABC | 3900 | 3912 | 26-03-2021 | 637.20 | ||
9 | 29 | PORTAL | 39XHHPH4528G1XX | ABC | 3912 | 3912 | 26-03-2021 | 637.20 | ||
10 | 30 | BOOKS | 39XHHPH4528G1XX | ABC | 81 | 81 | 19-05-2020 | 1829.00 | ||
11 | 2 | PORTAL | 39XHHPH4528G1XX | ABC | 560 | 560 | 01-07-2020 | 1837.50 | ||
12 | 41 | BOOKS | 39XHHPH4528G1XX | ABC | 560 | 560 | 01-07-2020 | 1837.50 | ||
13 | 46 | BOOKS | 39XHHPH4528G1XX | ABC | 560 | 1755 | 08-10-2020 | 1837.50 | ||
14 | 9 | PORTAL | 39XHHPH4528G1XX | ABC | 1755 | 1755 | 08-10-2020 | 1837.50 | ||
15 | 15 | PORTAL | 39XHHPH4528G1XX | ABC | 2290 | 2290 | 21-11-2020 | 1968.76 | ||
16 | 49 | BOOKS | 39XHHPH4528G1XX | ABC | 2290 | 2290 | 21-11-2020 | 1968.76 | ||
17 | 1 | PORTAL | 39XHHPH4528G1XX | ABC | 233 | 233 | 01-06-2020 | 1995.00 | ||
18 | 40 | BOOKS | 39XHHPH4528G1XX | ABC | 233 | 233 | 01-06-2020 | 1995.00 | ||
19 | 48 | BOOKS | 39XHHPH4528G1XX | ABC | 233 | 2210 | 13-11-2020 | 1995.00 | ||
20 | 14 | PORTAL | 39XHHPH4528G1XX | ABC | 2210 | 2210 | 13-11-2020 | 1995.00 | ||
21 | 4 | PORTAL | 39XHHPH4528G1XX | ABC | 744 | 744 | 18-07-2020 | 2768.76 | ||
22 | 33 | BOOKS | 39XHHPH4528G1XX | ABC | 744 | 744 | 18-07-2020 | 2768.76 | ||
23 | 8 | PORTAL | 39XHHPH4528G1XX | ABC | 1725 | 1725 | 06-10-2020 | 2992.50 | ||
24 | 45 | BOOKS | 39XHHPH4528G1XX | ABC | 1725 | 1725 | 06-10-2020 | 2992.50 | ||
25 | 50 | BOOKS | 39XHHPH4528G1XX | ABC | 1725 | 2450 | 04-12-2020 | 2992.50 | ||
26 | 51 | BOOKS | 39XHHPH4528G1XX | ABC | 1725 | 2677 | 24-12-2020 | 2992.50 | ||
27 | 55 | BOOKS | 39XHHPH4528G1XX | ABC | 1725 | 3387 | 17-02-2021 | 2992.50 | ||
28 | 56 | BOOKS | 39XHHPH4528G1XX | ABC | 1725 | 3419 | 19-02-2021 | 2992.50 | ||
29 | 17 | PORTAL | 39XHHPH4528G1XX | ABC | 2450 | 2450 | 04-12-2020 | 2992.50 | ||
30 | 18 | PORTAL | 39XHHPH4528G1XX | ABC | 2677 | 2677 | 24-12-2020 | 2992.50 | ||
31 | 23 | PORTAL | 39XHHPH4528G1XX | ABC | 3386 | 3386 | 17-02-2021 | 2992.50 | ||
32 | 24 | PORTAL | 39XHHPH4528G1XX | ABC | 3418 | 3418 | 19-02-2021 | 2992.50 | ||
33 | 31 | BOOKS | 39XHHPH4528G1XX | ABC | 213 | 213 | 29-05-2020 | 5347.18 | ||
34 | 6 | PORTAL | 39XHHPH4528G1XX | ABC | 988 | 988 | 06-08-2020 | 5460.00 | ||
35 | 43 | BOOKS | 39XHHPH4528G1XX | ABC | 988 | 988 | 06-08-2020 | 5460.00 | ||
36 | 47 | BOOKS | 39XHHPH4528G1XX | ABC | 988 | 1866 | 16-10-2020 | 5460.00 | ||
37 | 57 | BOOKS | 39XHHPH4528G1XX | ABC | 988 | 3482 | 25-02-2021 | 5460.00 | ||
38 | 10 | PORTAL | 39XHHPH4528G1XX | ABC | 1866 | 1866 | 16-10-2020 | 5460.00 | ||
39 | 25 | PORTAL | 39XHHPH4528G1XX | ABC | 3481 | 3481 | 25-02-2021 | 5460.00 | ||
40 | 26 | PORTAL | 39XHHPH4528G1XX | ABC | 3518 | 3518 | 27-02-2021 | 8850.00 | ||
41 | 39 | BOOKS | 39XHHPH4528G1XX | ABC | 3518 | 3519 | 27-02-2021 | 8850.00 | ||
42 | 36 | BOOKS | 39XHHPH4528G1XX | ABC | 2122 | 2122 | 07-11-2020 | 9180.00 | ||
43 | 13 | PORTAL | 39XHHPH4528G1XX | ABC | 2122 | 2122 | 07-11-2020 | 9180.40 | ||
44 | 37 | BOOKS | 39XHHPH4528G1XX | ABC | 2122 | 2442 | 04-12-2020 | 9180.40 | ||
45 | 16 | PORTAL | 39XHHPH4528G1XX | ABC | 2442 | 2442 | 04-12-2020 | 9180.40 | ||
46 | 11 | PORTAL | 39XHHPH4528G1XX | ABC | 1966 | 1966 | 24-10-2020 | 10164.76 | ||
47 | 34 | BOOKS | 39XHHPH4528G1XX | ABC | 1966 | 1966 | 24-10-2020 | 10164.76 | ||
48 | 5 | PORTAL | 39XHHPH4528G1XX | ABC | 950 | 950 | 04-08-2020 | 10500.00 | ||
49 | 42 | BOOKS | 39XHHPH4528G1XX | ABC | 950 | 950 | 04-08-2020 | 10500.00 | ||
50 | 44 | BOOKS | 39XHHPH4528G1XX | ABC | 950 | 1487 | 14-09-2020 | 10500.00 | ||
51 | 53 | BOOKS | 39XHHPH4528G1XX | ABC | 950 | 3047 | 22-01-2021 | 10500.00 | ||
52 | 54 | BOOKS | 39XHHPH4528G1XX | ABC | 950 | 3293 | 10-02-2021 | 10500.00 | ||
53 | 58 | BOOKS | 39XHHPH4528G1XX | ABC | 950 | 3806 | 19-03-2021 | 10500.00 | ||
54 | 7 | PORTAL | 39XHHPH4528G1XX | ABC | 1487 | 1487 | 14-09-2020 | 10500.00 | ||
55 | 20 | PORTAL | 39XHHPH4528G1XX | ABC | 3047 | 3047 | 22-01-2021 | 10500.00 | ||
56 | 22 | PORTAL | 39XHHPH4528G1XX | ABC | 3292 | 3292 | 10-02-2021 | 10500.00 | ||
57 | 27 | PORTAL | 39XHHPH4528G1XX | ABC | 3806 | 3806 | 19-03-2021 | 10500.00 | ||
58 | 3 | PORTAL | 39XHHPH4528G1XX | ABC | 576 | 576 | 02-07-2020 | 10502.00 | ||
59 | 32 | BOOKS | 39XHHPH4528G1XX | ABC | 576 | 576 | 02-07-2020 | 10502.00 | ||
60 | 35 | BOOKS | 39XHHPH4528G1XX | ABC | 576 | 2019 | 31-10-2020 | 10502.00 | ||
61 | 12 | PORTAL | 39XHHPH4528G1XX | ABC | 2019 | 2019 | 31-10-2020 | 10502.00 | ||
62 | 63 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3921 | XYZ3921 | 15-07-2020 | 8408.00 | ||
63 | 85 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3921 | 3921 | 15-07-2020 | 8408.00 | ||
64 | 61 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3917 | XYZ3917 | 13-07-2020 | 8850.00 | ||
65 | 84 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3917 | 3917 | 13-07-2020 | 8850.00 | ||
66 | 86 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3917 | 4045 | 05-01-2021 | 8850.00 | ||
67 | 87 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3917 | 3887 | 11-05-2020 | 8850.00 | ||
68 | 75 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4045 | XYZ4045 | 05-01-2021 | 8850.00 | ||
69 | 70 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4006 | XYZ4006 | 27-11-2020 | 22420.00 | ||
70 | 97 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ4006 | 4006 | 27-11-2020 | 22420.00 | ||
71 | 65 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3949 | XYZ3949 | 01-09-2020 | 25960.00 | ||
72 | 92 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3949 | 3949 | 01-09-2020 | 25960.00 | ||
73 | 101 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3949 | 4044 | 04-01-2021 | 25960.00 | ||
74 | 74 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4044 | XYZ4044 | 04-01-2021 | 25960.00 | ||
75 | 69 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3995 | XYZ3995 | 23-10-2020 | 43188.00 | ||
76 | 96 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3995 | 3995 | 23-10-2020 | 43188.00 | ||
77 | 67 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3970 | XYZ3970 | 30-09-2020 | 51920.00 | ||
78 | 94 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3970 | 3970 | 01-10-2020 | 51920.00 | ||
79 | 99 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3970 | 4027 | 22-12-2020 | 51920.00 | ||
80 | 100 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3970 | 4035 | 25-12-2020 | 51920.00 | ||
81 | 102 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3970 | 4049 | 09-01-2021 | 51920.00 | ||
82 | 72 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4027 | XYZ4027 | 22-12-2020 | 51920.00 | ||
83 | 73 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4035 | XYZ4035 | 25-12-2020 | 51920.00 | ||
84 | 76 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4049 | XYZ4049 | 09-01-2021 | 51920.00 | ||
85 | 68 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3976 | XYZ3976 | 13-10-2020 | 77880.00 | ||
86 | 95 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3976 | 3976 | 13-10-2020 | 77880.00 | ||
87 | 103 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3976 | 4050 | 13-01-2021 | 77880.00 | ||
88 | 104 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3976 | 4066 | 04-02-2021 | 77880.00 | ||
89 | 105 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3976 | 4074 | 12-02-2021 | 77880.00 | ||
90 | 77 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4050 | XYZ4050 | 13-01-2021 | 77880.00 | ||
91 | 78 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4066 | XYZ4066 | 04-02-2021 | 77880.00 | ||
92 | 79 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4074 | XYZ4074 | 12-02-2021 | 77880.00 | ||
93 | 83 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4111 | XYZ4111 | 31-03-2021 | 83780.00 | ||
94 | 109 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ4111 | 4111 | 31-03-2021 | 83780.00 | ||
95 | 66 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3951 | XYZ3951 | 04-09-2020 | 103840.00 | ||
96 | 93 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3951 | 3951 | 04-09-2020 | 103840.00 | ||
97 | 62 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3918 | XYZ3918 | 14-07-2020 | 129800.00 | ||
98 | 88 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 3886 | 11-05-2020 | 129800.00 | ||
99 | 89 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 3889 | 16-05-2020 | 129800.00 | ||
100 | 90 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 3918 | 14-07-2020 | 129800.00 | ||
101 | 91 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 3934 | 04-08-2020 | 129800.00 | ||
102 | 98 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 4024 | 15-12-2020 | 129800.00 | ||
103 | 106 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 4086 | 01-03-2021 | 129800.00 | ||
104 | 107 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 4093 | 08-03-2021 | 129800.00 | ||
105 | 108 | BOOKS | 39XDKPN0022Q1XT | XYZ | XYZ3918 | 4100 | 15-03-2021 | 129800.00 | ||
106 | 64 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ3934 | XYZ3934 | 04-08-2020 | 129800.00 | ||
107 | 71 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4024 | XYZ4024 | 15-12-2020 | 129800.00 | ||
108 | 80 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4086 | XYZ4086 | 27-02-2021 | 129800.00 | ||
109 | 81 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4093 | XYZ4093 | 08-03-2021 | 129800.00 | ||
110 | 82 | PORTAL | 39XDKPN0022Q1XT | XYZ | XYZ4100 | XYZ4100 | 15-03-2021 | 129800.00 | ||
Portal + Tally |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E2:E110 | Expression | =COUNTIFS(D$2:D$20000,D2,E$2:E$20000,E2)>2 | text | NO |
H2:I110 | Expression | =COUNTIFS(D$2:D$19705,D2,H$2:H$19705,H2)>3 | text | NO |
Last edited: