Index and Aggregate error

[Mo7a86]

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
	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

 

[Fluff]

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.

 

[Mo7a86]

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

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mo7a86]

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

 

[Fluff]

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.