Dear All
I have Huge excel sheet full of data the smallest one got 32500+ rows and largest sheets reaching more than 5000000 rows and i am try to do it searchable to enter account number and have all the matches to be reflected in another sheet which showing only true matches So i added a column with search function to have a true and false results then I want to index only true results to another sheet using index and aggregate ... now i copied a sample from the rows which find the same problem too any true results in December is not showing in the second sheets how cam i fix it ?
here is a sample from the 1st sheet
and here is a sample from the 2nd sheet
FYI my work device has office 2013 but now i am using home device you may notice it is office 360 so i want solution for office 2013
I have Huge excel sheet full of data the smallest one got 32500+ rows and largest sheets reaching more than 5000000 rows and i am try to do it searchable to enter account number and have all the matches to be reflected in another sheet which showing only true matches So i added a column with search function to have a true and false results then I want to index only true results to another sheet using index and aggregate ... now i copied a sample from the rows which find the same problem too any true results in December is not showing in the second sheets how cam i fix it ?
here is a sample from the 1st sheet
Sample.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | TYPE ACCOUNT NUMBER HERE >>> | 6.93794E+11 | TYPE ACCOUNT NUMBER THEN PRESS CTRL+ALT+L | DEBIT QATCH 2012 تحويلات إلى حسابات أخرى داخل قطر | |||||||||||||||
2 | المبلغ بالريال القطري | اسم البنك | اسم صاحب الحساب المحول له | التاريخ | الرقم | Cond | Debtor Name | Debtor Account | |||||||||||
3 | 2585 | W Bank | Abdul | 1/2/2012 7:52 | 1 | FALSE | ORYX | 693793733336 | |||||||||||
4 | 6599 | W Bank | Lopez | 1/2/2012 7:52 | 2 | FALSE | ORYX | 693793733336 | |||||||||||
5 | 3875 | W Bank | Bahadur | 1/2/2012 7:52 | 3 | FALSE | ORYX | 693793733336 | |||||||||||
6 | 1691 | W Bank | Ibunu | 1/2/2012 7:52 | 4 | FALSE | ORYX | 693793733336 | |||||||||||
7 | 1656 | W Bank | Gamage | 1/2/2012 7:52 | 5 | FALSE | ORYX | 693793733336 | |||||||||||
8 | 2399 | W Bank | Udayakumar | 1/2/2012 7:52 | 6 | FALSE | ORYX | 693793733336 | |||||||||||
9 | 1531 | W Bank | Seyad | 1/2/2012 7:52 | 7 | FALSE | ORYX | 693793733336 | |||||||||||
10 | 2899 | W Bank | Tiwar | 1/2/2012 7:52 | 8 | FALSE | ORYX | 693793733336 | |||||||||||
11 | 1669 | W Bank | Ramaiya | 1/2/2012 7:52 | 9 | FALSE | ORYX | 693793733336 | |||||||||||
12 | 8999 | W Bank | Mahendra | 1/2/2012 7:52 | 10 | FALSE | ORYX | 693793733336 | |||||||||||
13 | 1869 | W Bank | Sarath | 1/2/2012 7:52 | 11 | FALSE | ORYX | 693793733336 | |||||||||||
14 | 3899 | W Bank | Mohamed | 1/2/2012 7:52 | 12 | FALSE | ORYX | 693793733336 | |||||||||||
15 | 1599 | W Bank | Abdul | 5/29/2012 12:31 | 13 | TRUE | ASPIRE | 693793746969 | |||||||||||
16 | 21859 | W Bank | Dorothy | 5/29/2012 12:31 | 14 | TRUE | ASPIRE | 693793746969 | |||||||||||
17 | 2999 | W Bank | Ian | 5/29/2012 12:31 | 15 | TRUE | ASPIRE | 693793746969 | |||||||||||
18 | 1995 | W Bank | Kushan | 5/29/2012 12:31 | 16 | TRUE | ASPIRE | 693793746969 | |||||||||||
19 | 1995 | W Bank | Lalith | 5/29/2012 12:31 | 17 | TRUE | ASPIRE | 693793746969 | |||||||||||
20 | 2199 | W Bank | Manoj | 5/29/2012 12:31 | 18 | TRUE | ASPIRE | 693793746969 | |||||||||||
21 | 1669 | W Bank | Ma. | 5/29/2012 12:31 | 19 | TRUE | ASPIRE | 693793746969 | |||||||||||
22 | 2999 | W Bank | Ahmed | 5/29/2012 12:31 | 20 | TRUE | ASPIRE | 693793746969 | |||||||||||
23 | 1669 | W Bank | Pryncess | 5/29/2012 12:31 | 21 | TRUE | ASPIRE | 693793746969 | |||||||||||
24 | 2356 | W Bank | Abram | 5/29/2012 12:31 | 22 | TRUE | ASPIRE | 693793746969 | |||||||||||
25 | 2999 | W Bank | Subrota | 5/29/2012 12:31 | 23 | TRUE | ASPIRE | 693793746969 | |||||||||||
26 | 2839 | W Bank | Bilel | 5/29/2012 12:31 | 24 | TRUE | ASPIRE | 693793746969 | |||||||||||
27 | 1353 | W Bank | Abbas | 5/29/2012 12:31 | 25 | TRUE | ASPIRE | 693793746969 | |||||||||||
28 | 3199 | W Bank | Muhammad | 5/29/2012 12:31 | 26 | TRUE | ASPIRE | 693793746969 | |||||||||||
29 | 2999 | W Bank | Emmy | 5/29/2012 12:31 | 27 | TRUE | ASPIRE | 693793746969 | |||||||||||
30 | 1669 | W Bank | Allan | 5/29/2012 12:31 | 28 | TRUE | ASPIRE | 693793746969 | |||||||||||
31 | 87551.99 | I Bank | Afra | 7/15/2012 12:56 | 29 | FALSE | AL-MAALY | 693793768895 | |||||||||||
32 | 67681.59 | I Bank | Noza | 7/15/2012 12:56 | 30 | FALSE | AL-MAALY | 693793768895 | |||||||||||
33 | 89987.99 | I Bank | Fatima | 7/15/2012 12:56 | 31 | FALSE | AL-MAALY | 693793768895 | |||||||||||
34 | 12759 | I Bank | Fozea | 7/15/2012 12:56 | 32 | FALSE | AL-MAALY | 693793768895 | |||||||||||
35 | 192785.36 | I Bank | ZAINAB | 7/15/2012 12:56 | 33 | FALSE | AL-MAALY | 693793768895 | |||||||||||
36 | 111273.99 | I Bank | Dahaba | 7/15/2012 12:56 | 34 | FALSE | AL-MAALY | 693793768895 | |||||||||||
37 | 83697.36 | I Bank | Wafa | 7/15/2012 12:56 | 35 | FALSE | AL-MAALY | 693793768895 | |||||||||||
38 | 111623.99 | I Bank | Mubaraka | 7/15/2012 12:56 | 36 | FALSE | AL-MAALY | 693793768895 | |||||||||||
39 | 126966.81 | I Bank | Kholoud | 7/15/2012 12:56 | 37 | FALSE | AL-MAALY | 693793768895 | |||||||||||
40 | 126583.99 | H Bank | Reem | 7/15/2012 12:56 | 38 | FALSE | AL-MAALY | 693793768895 | |||||||||||
41 | 162259.66 | I Bank | Moza | 7/15/2012 12:56 | 39 | FALSE | AL-MAALY | 693793768895 | |||||||||||
42 | 155999.66 | I Bank | Maryam | 7/15/2012 12:56 | 40 | FALSE | AL-MAALY | 693793768895 | |||||||||||
43 | 92369.67 | I Bank | Haya | 7/15/2012 12:56 | 41 | FALSE | AL-MAALY | 693793768895 | |||||||||||
44 | 121392.51 | I Bank | Fozea | 7/15/2012 12:56 | 42 | FALSE | AL-MAALY | 693793768895 | |||||||||||
45 | 39933.6 | F Bank Ltd. | SHAIKHA | 7/15/2012 12:56 | 43 | FALSE | AL-MAALY | 693793768895 | |||||||||||
46 | 61666.97 | F Bank Ltd. | Wadha | 7/15/2012 12:56 | 44 | FALSE | AL-MAALY | 693793768895 | |||||||||||
47 | 27571.73 | F Bank Ltd. | Haleema | 7/15/2012 12:56 | 45 | FALSE | AL-MAALY | 693793768895 | |||||||||||
48 | 77858.51 | F Bank Ltd. | ROWAIDA | 7/15/2012 12:56 | 46 | FALSE | AL-MAALY | 693793768895 | |||||||||||
49 | 59529.91 | P Bank Ltd. | AMEER | 7/15/2012 12:56 | 47 | FALSE | AL-MAALY | 693793768895 | |||||||||||
50 | 6659 | P Bank Ltd. | Gada | 7/15/2012 12:56 | 48 | FALSE | AL-MAALY | 693793768895 | |||||||||||
51 | 33289 | P Bank Ltd. | Shayma | 7/15/2012 12:56 | 49 | FALSE | AL-MAALY | 693793768895 | |||||||||||
52 | 28689 | P Bank Ltd. | Hanan | 7/15/2012 12:56 | 50 | FALSE | AL-MAALY | 693793768895 | |||||||||||
53 | 36289 | X Bank Plc. | Azza | 7/15/2012 12:56 | 51 | FALSE | AL-MAALY | 693793768895 | |||||||||||
54 | 31729 | H Bank | Kaouther | 7/15/2012 12:56 | 52 | FALSE | AL-MAALY | 693793768895 | |||||||||||
55 | 95125.33 | I Bank | Aisha | 7/15/2012 12:56 | 53 | FALSE | AL-MAALY | 693793768895 | |||||||||||
56 | 113879.18 | O Bank | Aisha | 7/15/2012 12:56 | 54 | FALSE | AL-MAALY | 693793768895 | |||||||||||
57 | 26889 | O Bank | Hanan | 7/15/2012 12:56 | 55 | FALSE | AL-MAALY | 693793768895 | |||||||||||
58 | 192966.66 | O Bank | Wadha | 7/15/2012 12:56 | 56 | FALSE | AL-MAALY | 693793768895 | |||||||||||
59 | 79921.96 | O Bank | Fatma | 7/15/2012 12:56 | 57 | FALSE | AL-MAALY | 693793768895 | |||||||||||
60 | 61599.99 | O Bank | Hissa | 7/15/2012 12:56 | 58 | FALSE | AL-MAALY | 693793768895 | |||||||||||
61 | 86972.76 | O Bank | Jawaher | 7/15/2012 12:56 | 59 | FALSE | AL-MAALY | 693793768895 | |||||||||||
62 | 16689 | O Bank | Mays | 7/15/2012 12:56 | 60 | FALSE | AL-MAALY | 693793768895 | |||||||||||
63 | 83775.79 | O Bank | Najat | 7/15/2012 12:56 | 61 | FALSE | AL-MAALY | 693793768895 | |||||||||||
64 | 37389.66 | O Bank | Noora | 7/15/2012 12:56 | 62 | FALSE | AL-MAALY | 693793768895 | |||||||||||
65 | 29816.92 | O Bank | Gemza | 7/15/2012 12:56 | 63 | FALSE | AL-MAALY | 693793768895 | |||||||||||
66 | 196926.66 | O Bank | Kholoud | 7/15/2012 12:56 | 64 | TRUE | ASPIRE | 693793746969 | |||||||||||
67 | 111763.99 | O Bank | Wasmya | 7/15/2012 12:56 | 65 | FALSE | AL-MAALY | 693793768895 | |||||||||||
68 | 112963.99 | O Bank | Refaa | 7/15/2012 12:56 | 66 | FALSE | AL-MAALY | 693793768895 | |||||||||||
69 | 73252.26 | I Bank | Salma | 7/15/2012 12:56 | 67 | FALSE | AL-MAALY | 693793768895 | |||||||||||
70 | 198838 | O Bank | hesa | 7/15/2012 12:56 | 68 | FALSE | AL-MAALY | 693793768895 | |||||||||||
71 | 3768 | E Bank PSC. | AYUB | 12/31/2012 12:28 | 69 | FALSE | DAR | 192792791537 | |||||||||||
72 | 29665 | P Bank Ltd. | AYMAN | 12/31/2012 12:28 | 70 | FALSE | DAR | 192792791537 | |||||||||||
73 | 19599 | P Bank Ltd. | AHMED | 12/31/2012 12:28 | 71 | FALSE | DAR | 192792791537 | |||||||||||
74 | 9817 | P Bank Ltd. | MOHAMMED | 12/31/2012 12:28 | 72 | FALSE | DAR | 192792791537 | |||||||||||
75 | 9766 | F Bank Ltd. | ROBERT | 12/31/2012 12:28 | 73 | FALSE | DAR | 192792791537 | |||||||||||
76 | 9623 | E Bank PSC. | FAHD | 12/31/2012 12:28 | 74 | FALSE | DAR | 192792791537 | |||||||||||
77 | 5696 | E Bank PSC. | SALAH | 12/31/2012 12:28 | 75 | FALSE | DAR | 192792791537 | |||||||||||
78 | 7999 | E Bank PSC. | JAMALU | 12/31/2012 12:28 | 76 | FALSE | DAR | 192792791537 | |||||||||||
79 | 19872 | E Bank PSC. | GHULAM | 12/31/2012 12:28 | 77 | FALSE | DAR | 192792791537 | |||||||||||
80 | 8999 | E Bank PSC. | MAHMOUD | 12/31/2012 12:28 | 78 | FALSE | DAR | 192792791537 | |||||||||||
81 | 7999 | E Bank PSC. | DILEEP | 12/31/2012 12:28 | 79 | FALSE | DAR | 192792791537 | |||||||||||
82 | 1955 | E Bank PSC. | ASHRAF | 12/31/2012 12:28 | 80 | FALSE | DAR | 192792791537 | |||||||||||
83 | 3319 | E Bank PSC. | MOHAMMED | 12/31/2012 12:28 | 81 | FALSE | DAR | 192792791537 | |||||||||||
84 | 19569 | E Bank PSC. | RAJEESH | 12/31/2012 12:28 | 82 | FALSE | DAR | 192792791537 | |||||||||||
85 | 6978 | E Bank PSC. | SAWAI | 12/31/2012 12:28 | 83 | FALSE | DAR | 192792791537 | |||||||||||
86 | 29999 | E Bank PSC. | HATHAM | 12/31/2012 12:28 | 84 | FALSE | DAR | 192792791537 | |||||||||||
87 | 7796 | E Bank PSC. | ROBIN | 12/31/2012 12:28 | 85 | FALSE | DAR | 192792791537 | |||||||||||
88 | 6226 | E Bank PSC. | SURENDRA | 12/31/2012 12:28 | 86 | FALSE | DAR | 192792791537 | |||||||||||
89 | 16659 | P Bank Ltd. | KARIM | 12/31/2012 12:28 | 87 | FALSE | DAR | 192792791537 | |||||||||||
90 | 39399 | B Bank | AHMED | 12/31/2012 12:32 | 88 | FALSE | AHMED | 192792736291 | |||||||||||
91 | 5999 | J Bank | WALEED | 12/31/2012 12:32 | 89 | FALSE | IMAD | 192792781262 | |||||||||||
92 | 7599 | W Bank | AIMAN | 12/31/2012 12:32 | 90 | FALSE | AYMAN | 192792738040 | |||||||||||
93 | 1799 | O Bank | MARYNOR | 12/31/2012 12:32 | 91 | FALSE | BERNARDO | 192792798307 | |||||||||||
94 | 2599 | P Bank Ltd. | EMAD | 12/31/2012 12:32 | 92 | FALSE | IMAD | 192792781262 | |||||||||||
95 | 9165 | P Bank Ltd. | PARK | 12/31/2012 12:32 | 93 | FALSE | KHALID | 192792757827 | |||||||||||
96 | 19999 | W Bank | HAMAD | 12/31/2012 12:32 | 94 | FALSE | HAMAD | 192792710039 | |||||||||||
97 | 797 | F Bank Ltd. | M | 12/31/2012 12:33 | 95 | TRUE | ASPIRE | 693793746969 | |||||||||||
98 | 2599 | F Bank Ltd. | MANIKANDAN | 12/31/2012 12:33 | 96 | FALSE | GULF | 192792722632 | |||||||||||
99 | 855.85 | F Bank Ltd. | JITENDRA | 12/31/2012 12:33 | 97 | FALSE | GULF | 192792722632 | |||||||||||
100 | 1325 | F Bank Ltd. | KRISHANA | 12/31/2012 12:33 | 98 | FALSE | GULF | 192792722632 | |||||||||||
101 | 1551.75 | F Bank Ltd. | MOHAMMAD | 12/31/2012 12:33 | 99 | FALSE | GULF | 192792722632 | |||||||||||
102 | 1287.5 | F Bank Ltd. | AMBIKA | 12/31/2012 12:33 | 100 | FALSE | GULF | 192792722632 | |||||||||||
103 | 1295 | F Bank Ltd. | RAMESH | 12/31/2012 12:33 | 101 | FALSE | GULF | 192792722632 | |||||||||||
104 | 1239.75 | F Bank Ltd. | Y. | 12/31/2012 12:33 | 102 | FALSE | GULF | 192792722632 | |||||||||||
105 | 17759 | F Bank Ltd. | R. | 12/31/2012 12:33 | 103 | FALSE | GULF | 192792722632 | |||||||||||
106 | 2186.33 | F Bank Ltd. | SAMIULLA | 12/31/2012 12:33 | 104 | FALSE | GULF | 192792722632 | |||||||||||
107 | 2266.75 | F Bank Ltd. | A. | 12/31/2012 12:33 | 105 | FALSE | GULF | 192792722632 | |||||||||||
108 | 2733.33 | F Bank Ltd. | K.JAYAPRAKASH | 12/31/2012 12:33 | 106 | FALSE | GULF | 192792722632 | |||||||||||
109 | 159 | C Bank | SHEIKH | 12/31/2012 12:54 | 107 | TRUE | ASPIRE | 693793746969 | |||||||||||
110 | 1299 | C Bank | SHEIKH | 12/31/2012 12:54 | 108 | FALSE | MAMOON | 192792744584 | |||||||||||
111 | 159 | C Bank | SHEIKH | 12/31/2012 12:54 | 109 | FALSE | AHMED | 192792743352 | |||||||||||
112 | 399 | C Bank | SHEIKH | 12/31/2012 12:54 | 110 | FALSE | JAMAL | 192792740083 | |||||||||||
113 | 12999 | F Bank Ltd. | NULL | 12/31/2012 13:23 | 111 | TRUE | ASPIRE | 693793746969 | |||||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I3:I113 | I3 | =AGGREGATE(3,7,G$3:G3) |
J3:J113 | J3 | =ISNUMBER((SEARCH($B$1,$Q$3:$Q$8834))) |
and here is a sample from the 2nd sheet
Sample.xlsx | |||||||
---|---|---|---|---|---|---|---|
E | F | G | H | I | |||
1 | DEBIT QATCH 2012 تحويلات إلى حسابات أخرى داخل قطر | ||||||
2 | المبلغ بالريال القطري | اسم البنك | اسم صاحب الحساب المحول له | التاريخ | الرقم | ||
3 | 1599 | W Bank | Abdul | 29-May-2012 | 13 | ||
4 | 21859 | W Bank | Dorothy | 29-May-2012 | 14 | ||
5 | 2999 | W Bank | Ian | 29-May-2012 | 15 | ||
6 | 1995 | W Bank | Kushan | 29-May-2012 | 16 | ||
7 | 1995 | W Bank | Lalith | 29-May-2012 | 17 | ||
8 | 2199 | W Bank | Manoj | 29-May-2012 | 18 | ||
9 | 1669 | W Bank | Ma. | 29-May-2012 | 19 | ||
10 | 2999 | W Bank | Ahmed | 29-May-2012 | 20 | ||
11 | 1669 | W Bank | Pryncess | 29-May-2012 | 21 | ||
12 | 2356 | W Bank | Abram | 29-May-2012 | 22 | ||
13 | 2999 | W Bank | Subrota | 29-May-2012 | 23 | ||
14 | 2839 | W Bank | Bilel | 29-May-2012 | 24 | ||
15 | 1353 | W Bank | Abbas | 29-May-2012 | 25 | ||
16 | 3199 | W Bank | Muhammad | 29-May-2012 | 26 | ||
17 | 2999 | W Bank | Emmy | 29-May-2012 | 27 | ||
18 | 1669 | W Bank | Allan | 29-May-2012 | 28 | ||
19 | 196926.66 | O Bank | Kholoud | 15-Jul-2012 | 64 | ||
20 | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | ||
21 | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | ||
22 | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | ||
23 | #NUM! | #NUM! | #NUM! | #NUM! | #NUM! | ||
Filter |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:G23 | E3 | =INDEX(Data!E3:$J$113,AGGREGATE(15,7,(Data!$Q$2:$Q$70=Data!$B$1)/(Data!$Q$2:$Q$70=Data!$B$1)*(ROW(Data!$Q$2:$Q$70)-ROW(Data!$Q2)),ROWS(Filter!$K$3:K3)),1) |
H3:H23 | H3 | =INDEX(Data!H3:$J$113,AGGREGATE(15,7,(Data!$Q$2:$Q$70=Data!$B$1)/(Data!$Q$2:$Q$70=Data!$B$1)*(ROW(Data!$Q$2:$Q$70)-ROW(Data!$Q2)),ROWS(Filter!$K$3:M3)),1) |
I3 | I3 | =@INDEX(Data!I3:$J$113,AGGREGATE(15,7,(Data!$Q$2:$Q$70=Data!$B$1)/(Data!$Q$2:$Q$70=Data!$B$1)*(ROW(Data!$Q$2:$Q$70)-ROW(Data!$Q2)),ROWS(Filter!$K$3:K3)),1) |
I4:I23 | I4 | =INDEX(Data!I4:$J$113,AGGREGATE(15,7,(Data!$Q$2:$Q$70=Data!$B$1)/(Data!$Q$2:$Q$70=Data!$B$1)*(ROW(Data!$Q$2:$Q$70)-ROW(Data!$Q3)),ROWS(Filter!$K$3:K4)),1) |
FYI my work device has office 2013 but now i am using home device you may notice it is office 360 so i want solution for office 2013
Last edited by a moderator: