Index and Aggregate error

M

Mo7a86

New Member
Joined
Jun 14, 2022
Messages
8
Office Version
  1. 2013
Platform
  1. Windows
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

Sample.xlsx
ABCDEFGHIJKLMNOPQ
1TYPE ACCOUNT NUMBER HERE >>>6.93794E+11TYPE ACCOUNT NUMBER THEN PRESS CTRL+ALT+LDEBIT QATCH 2012 تحويلات إلى حسابات أخرى داخل قطر
2المبلغ بالريال القطرياسم البنكاسم صاحب الحساب المحول لهالتاريخالرقمCondDebtor NameDebtor Account
32585W Bank Abdul1/2/2012 7:521FALSEORYX693793733336
46599W Bank Lopez1/2/2012 7:522FALSEORYX693793733336
53875W Bank Bahadur1/2/2012 7:523FALSEORYX693793733336
61691W Bank Ibunu1/2/2012 7:524FALSEORYX693793733336
71656W Bank Gamage1/2/2012 7:525FALSEORYX693793733336
82399W Bank Udayakumar1/2/2012 7:526FALSEORYX693793733336
91531W Bank Seyad1/2/2012 7:527FALSEORYX693793733336
102899W Bank Tiwar1/2/2012 7:528FALSEORYX693793733336
111669W Bank Ramaiya1/2/2012 7:529FALSEORYX693793733336
128999W Bank Mahendra1/2/2012 7:5210FALSEORYX693793733336
131869W Bank Sarath1/2/2012 7:5211FALSEORYX693793733336
143899W Bank Mohamed1/2/2012 7:5212FALSEORYX693793733336
151599W Bank Abdul5/29/2012 12:3113TRUEASPIRE693793746969
1621859W Bank Dorothy5/29/2012 12:3114TRUEASPIRE693793746969
172999W Bank Ian5/29/2012 12:3115TRUEASPIRE693793746969
181995W Bank Kushan5/29/2012 12:3116TRUEASPIRE693793746969
191995W Bank Lalith5/29/2012 12:3117TRUEASPIRE693793746969
202199W Bank Manoj5/29/2012 12:3118TRUEASPIRE693793746969
211669W Bank Ma.5/29/2012 12:3119TRUEASPIRE693793746969
222999W Bank Ahmed5/29/2012 12:3120TRUEASPIRE693793746969
231669W Bank Pryncess5/29/2012 12:3121TRUEASPIRE693793746969
242356W Bank Abram5/29/2012 12:3122TRUEASPIRE693793746969
252999W Bank Subrota5/29/2012 12:3123TRUEASPIRE693793746969
262839W Bank Bilel5/29/2012 12:3124TRUEASPIRE693793746969
271353W Bank Abbas5/29/2012 12:3125TRUEASPIRE693793746969
283199W Bank Muhammad5/29/2012 12:3126TRUEASPIRE693793746969
292999W Bank Emmy5/29/2012 12:3127TRUEASPIRE693793746969
301669W Bank Allan5/29/2012 12:3128TRUEASPIRE693793746969
3187551.99 I Bank Afra7/15/2012 12:5629FALSEAL-MAALY693793768895
3267681.59 I Bank Noza7/15/2012 12:5630FALSEAL-MAALY693793768895
3389987.99 I Bank Fatima7/15/2012 12:5631FALSEAL-MAALY693793768895
3412759 I Bank Fozea7/15/2012 12:5632FALSEAL-MAALY693793768895
35192785.36 I Bank ZAINAB7/15/2012 12:5633FALSEAL-MAALY693793768895
36111273.99 I Bank Dahaba7/15/2012 12:5634FALSEAL-MAALY693793768895
3783697.36 I Bank Wafa7/15/2012 12:5635FALSEAL-MAALY693793768895
38111623.99 I Bank Mubaraka7/15/2012 12:5636FALSEAL-MAALY693793768895
39126966.81 I Bank Kholoud7/15/2012 12:5637FALSEAL-MAALY693793768895
40126583.99H Bank Reem7/15/2012 12:5638FALSEAL-MAALY693793768895
41162259.66 I Bank Moza7/15/2012 12:5639FALSEAL-MAALY693793768895
42155999.66 I Bank Maryam7/15/2012 12:5640FALSEAL-MAALY693793768895
4392369.67 I Bank Haya7/15/2012 12:5641FALSEAL-MAALY693793768895
44121392.51 I Bank Fozea7/15/2012 12:5642FALSEAL-MAALY693793768895
4539933.6F Bank Ltd. SHAIKHA7/15/2012 12:5643FALSEAL-MAALY693793768895
4661666.97F Bank Ltd. Wadha7/15/2012 12:5644FALSEAL-MAALY693793768895
4727571.73F Bank Ltd. Haleema7/15/2012 12:5645FALSEAL-MAALY693793768895
4877858.51F Bank Ltd. ROWAIDA7/15/2012 12:5646FALSEAL-MAALY693793768895
4959529.91P Bank Ltd. AMEER7/15/2012 12:5647FALSEAL-MAALY693793768895
506659P Bank Ltd. Gada7/15/2012 12:5648FALSEAL-MAALY693793768895
5133289P Bank Ltd. Shayma7/15/2012 12:5649FALSEAL-MAALY693793768895
5228689P Bank Ltd. Hanan7/15/2012 12:5650FALSEAL-MAALY693793768895
5336289X Bank Plc. Azza7/15/2012 12:5651FALSEAL-MAALY693793768895
5431729H Bank Kaouther7/15/2012 12:5652FALSEAL-MAALY693793768895
5595125.33 I Bank Aisha7/15/2012 12:5653FALSEAL-MAALY693793768895
56113879.18O Bank Aisha7/15/2012 12:5654FALSEAL-MAALY693793768895
5726889O Bank Hanan7/15/2012 12:5655FALSEAL-MAALY693793768895
58192966.66O Bank Wadha7/15/2012 12:5656FALSEAL-MAALY693793768895
5979921.96O Bank Fatma7/15/2012 12:5657FALSEAL-MAALY693793768895
6061599.99O Bank Hissa7/15/2012 12:5658FALSEAL-MAALY693793768895
6186972.76O Bank Jawaher7/15/2012 12:5659FALSEAL-MAALY693793768895
6216689O Bank Mays7/15/2012 12:5660FALSEAL-MAALY693793768895
6383775.79O Bank Najat7/15/2012 12:5661FALSEAL-MAALY693793768895
6437389.66O Bank Noora7/15/2012 12:5662FALSEAL-MAALY693793768895
6529816.92O Bank Gemza7/15/2012 12:5663FALSEAL-MAALY693793768895
66196926.66O Bank Kholoud7/15/2012 12:5664TRUEASPIRE693793746969
67111763.99O Bank Wasmya7/15/2012 12:5665FALSEAL-MAALY693793768895
68112963.99O Bank Refaa7/15/2012 12:5666FALSEAL-MAALY693793768895
6973252.26 I Bank Salma7/15/2012 12:5667FALSEAL-MAALY693793768895
70198838O Bank hesa7/15/2012 12:5668FALSEAL-MAALY693793768895
713768E Bank PSC. AYUB12/31/2012 12:2869FALSEDAR192792791537
7229665P Bank Ltd. AYMAN12/31/2012 12:2870FALSEDAR192792791537
7319599P Bank Ltd. AHMED12/31/2012 12:2871FALSEDAR192792791537
749817P Bank Ltd. MOHAMMED12/31/2012 12:2872FALSEDAR192792791537
759766F Bank Ltd. ROBERT12/31/2012 12:2873FALSEDAR192792791537
769623E Bank PSC. FAHD12/31/2012 12:2874FALSEDAR192792791537
775696E Bank PSC. SALAH12/31/2012 12:2875FALSEDAR192792791537
787999E Bank PSC. JAMALU12/31/2012 12:2876FALSEDAR192792791537
7919872E Bank PSC. GHULAM12/31/2012 12:2877FALSEDAR192792791537
808999E Bank PSC. MAHMOUD12/31/2012 12:2878FALSEDAR192792791537
817999E Bank PSC. DILEEP12/31/2012 12:2879FALSEDAR192792791537
821955E Bank PSC. ASHRAF12/31/2012 12:2880FALSEDAR192792791537
833319E Bank PSC. MOHAMMED12/31/2012 12:2881FALSEDAR192792791537
8419569E Bank PSC. RAJEESH12/31/2012 12:2882FALSEDAR192792791537
856978E Bank PSC. SAWAI12/31/2012 12:2883FALSEDAR192792791537
8629999E Bank PSC. HATHAM12/31/2012 12:2884FALSEDAR192792791537
877796E Bank PSC. ROBIN12/31/2012 12:2885FALSEDAR192792791537
886226E Bank PSC. SURENDRA12/31/2012 12:2886FALSEDAR192792791537
8916659P Bank Ltd. KARIM12/31/2012 12:2887FALSEDAR192792791537
9039399B Bank AHMED12/31/2012 12:3288FALSEAHMED192792736291
915999J Bank WALEED12/31/2012 12:3289FALSEIMAD192792781262
927599W Bank AIMAN12/31/2012 12:3290FALSEAYMAN192792738040
931799O Bank MARYNOR12/31/2012 12:3291FALSEBERNARDO192792798307
942599P Bank Ltd. EMAD12/31/2012 12:3292FALSEIMAD192792781262
959165P Bank Ltd. PARK12/31/2012 12:3293FALSEKHALID192792757827
9619999W Bank HAMAD12/31/2012 12:3294FALSEHAMAD192792710039
97797F Bank Ltd. M12/31/2012 12:3395TRUEASPIRE693793746969
982599F Bank Ltd. MANIKANDAN12/31/2012 12:3396FALSEGULF192792722632
99855.85F Bank Ltd. JITENDRA12/31/2012 12:3397FALSEGULF192792722632
1001325F Bank Ltd. KRISHANA12/31/2012 12:3398FALSEGULF192792722632
1011551.75F Bank Ltd. MOHAMMAD12/31/2012 12:3399FALSEGULF192792722632
1021287.5F Bank Ltd. AMBIKA12/31/2012 12:33100FALSEGULF192792722632
1031295F Bank Ltd. RAMESH12/31/2012 12:33101FALSEGULF192792722632
1041239.75F Bank Ltd. Y.12/31/2012 12:33102FALSEGULF192792722632
10517759F Bank Ltd. R.12/31/2012 12:33103FALSEGULF192792722632
1062186.33F Bank Ltd. SAMIULLA12/31/2012 12:33104FALSEGULF192792722632
1072266.75F Bank Ltd. A.12/31/2012 12:33105FALSEGULF192792722632
1082733.33F Bank Ltd. K.JAYAPRAKASH12/31/2012 12:33106FALSEGULF192792722632
109159C Bank SHEIKH12/31/2012 12:54107TRUEASPIRE693793746969
1101299C Bank SHEIKH12/31/2012 12:54108FALSEMAMOON192792744584
111159C Bank SHEIKH12/31/2012 12:54109FALSEAHMED192792743352
112399C Bank SHEIKH12/31/2012 12:54110FALSEJAMAL192792740083
11312999F Bank Ltd. NULL12/31/2012 13:23111TRUEASPIRE693793746969
Data
Cell Formulas
RangeFormula
I3:I113I3=AGGREGATE(3,7,G$3:G3)
J3:J113J3=ISNUMBER((SEARCH($B$1,$Q$3:$Q$8834)))


and here is a sample from the 2nd sheet

Sample.xlsx
EFGHI
1DEBIT QATCH 2012 تحويلات إلى حسابات أخرى داخل قطر
2المبلغ بالريال القطرياسم البنكاسم صاحب الحساب المحول لهالتاريخالرقم
31599W Bank Abdul29-May-201213
421859W Bank Dorothy29-May-201214
52999W Bank Ian29-May-201215
61995W Bank Kushan29-May-201216
71995W Bank Lalith29-May-201217
82199W Bank Manoj29-May-201218
91669W Bank Ma.29-May-201219
102999W Bank Ahmed29-May-201220
111669W Bank Pryncess29-May-201221
122356W Bank Abram29-May-201222
132999W Bank Subrota29-May-201223
142839W Bank Bilel29-May-201224
151353W Bank Abbas29-May-201225
163199W Bank Muhammad29-May-201226
172999W Bank Emmy29-May-201227
181669W Bank Allan29-May-201228
19196926.66O Bank Kholoud15-Jul-201264
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
RangeFormula
E3:G23E3=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:H23H3=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)
I3I3=@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:I23I4=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:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Two things spring to mind.
1) You need to lock the Row(Data!Q2)
2) your ranges are only going to Q70 they should be more than that.
 
Upvote 0
Solution
Hi fluff

It is work for the sample and for the main sheet but i had to make some adjustments in the main sheet there was lots of text and general format cells so i make them all numbers and adjust the array and it works
wow i feel it is silly question hehe thanks for the quick response and the hint

1655230188936.png
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi dear

First i am using office 2003 in my job

Again based on above case now i have finished 11 sheets with the same concept successfully i was wondering if the followinqg is possible :

Is it possible to combine all true results ftom 11 sheets in one sheet and be sorted by date withoutblank cells? I tried to copy the first sheet same as the above to a new sheet but when i close the source woorkbuks it gives value error i dont know if there is a solution to this

Can i retrieve data from closed workbooks?

Can i do it without copying the first sheet and with closed workbook o think the answer is no ?

Last but not least i was wondering if i can update the full data sheet on daily basis automatically if yes all files that i save daily should be in the same folder ?

Thanks
 
Upvote 0
I know of no way to combine all 4 sheets into 1 using formula with your version of xl. You would probably need a macro.
But as that is a different question it needs a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
607
Domenic
D
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
S
  • Solved
Data needed based on Drop down
Replies
4
Views
171
sksanjeev786
S
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
317
dshtesy
D
A
  • Solved
Filter data in listbox on form based on matching part of item in two comboboxes
Replies
9
Views
114
Ali M
A

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

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