Formula doesn't work as I want it

[viktor_david]

Guys. I need help, please

I have a list of trades in one tab and a another tab with the trades for only 1 customer.
The trades are ordered by date with all the customers together, and the only way I know of getting all the trades regarding certain customer (customer 2 in this example), without taking into accounts other customers, is by using this formula.

The problem arises when 2 different securities have the exact same quantity. If you see at Trades on the lines with yellow color, we have different securities and different orders (Buy/Sell), but the amount is the same, I get these 3 orders as if they were security 88 when they are 88,89,and 90
I would like to know how to fix this...

It is worth noting that from A1 to E8 is the table for the formulas I created,
F9 to R29 is more like the data table


Thanks a lot and hope someone can help me.

Excel 2010 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	B/S	Qty. Tot	Ticker	Qty.								10bp
2	02.05.2017	SELL	97290	Security84	1096								10bp + fee
3	03.05.2017	BUY	38370	Security85	286								5bp
4	06.05.2017	SELL	2164.97	Security88	155								5bp +fee
5	06.05.2017	SELL	2164.97	Security88	155								Special
6	06.05.2017	SELL	2164.97	Security88	155
7	#I/T	#I/T	#I/T	#I/T	#I/T
8						Date	K/S	antall	ticker					Cust. 1	Cust. 2	Cust. 3	Cust. 4	Cust. 5
9						29.04.2017	SELL	532	Security81								,532,
10						30.04.2017	SELL	16361	Security82								,1,622,
11						01.05.2017	SELL	66378	Security83					,5,011,				,2,836,
12						02.05.2017	SELL	97290	Security84					,7,263,	,1,096,			,4,109,
13						03.05.2017	BUY	38370	Security85					,2,308,	,286,			,1,625,
14						04.05.2017	SELL	7500	Security86
15						05.05.2017	SELL	1063.49	Security87
16						06.05.2017	SELL	2164.97	Security88						,155,
17						07.05.2017	SELL	1159.23	Security89
18						08.05.2017	BUY	4800	Security90						,155,
19						09.05.2017	BUY	300	Security91
20						10.05.2017	BUY	150	Security92								,150,
21						11.05.2017	BUY	3200	Security93
22						12.05.2017	BUY	1500	Security94
23						13.05.2017	SELL	317	Security95
24						14.05.2017	SELL	300	Security96
25						15.05.2017	SELL	5000	Security97
26						16.05.2017	BUY	13900	Security98
27						17.05.2017	BUY	10760	Security99						,155,
28						18.05.2017	BUY	2000	Security100
29						19.05.2017	BUY	26	Security101
Trades

Cell Formulas
Range	Formula
A2	{=INDEX(Trades!F1:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O2)),1),"")),Trades!$O1:$O$905,0)),Trades!F:F)}
A3	{=INDEX(Trades!F2:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O3)),1),"")),Trades!$O2:$O$905,0)),Trades!F:F)}
A4	{=INDEX(Trades!F3:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O4)),1),"")),Trades!$O3:$O$905,0)),Trades!F:F)}
A5	{=INDEX(Trades!F4:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O5)),1),"")),Trades!$O4:$O$905,0)),Trades!F:F)}
A6	{=INDEX(Trades!F5:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O6)),1),"")),Trades!$O5:$O$905,0)),Trades!F:F)}
A7	{=INDEX(Trades!F6:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O7)),1),"")),Trades!$O6:$O$905,0)),Trades!F:F)}
B2	{=INDEX(Trades!G1:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O2)),1),"")),Trades!$O1:$O$905,0)),Trades!G:G)}
B3	{=INDEX(Trades!G2:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O3)),1),"")),Trades!$O2:$O$905,0)),Trades!G:G)}
B4	{=INDEX(Trades!G3:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O4)),1),"")),Trades!$O3:$O$905,0)),Trades!G:G)}
B5	{=INDEX(Trades!G4:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O5)),1),"")),Trades!$O4:$O$905,0)),Trades!G:G)}
B6	{=INDEX(Trades!G5:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O6)),1),"")),Trades!$O5:$O$905,0)),Trades!G:G)}
B7	{=INDEX(Trades!G6:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O7)),1),"")),Trades!$O6:$O$905,0)),Trades!G:G)}
C2	{=INDEX(Trades!H1:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O2)),1),"")),Trades!$O1:$O$905,0)),Trades!H:H)}
C3	{=INDEX(Trades!H2:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O3)),1),"")),Trades!$O2:$O$905,0)),Trades!H:H)}
C4	{=INDEX(Trades!H3:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O4)),1),"")),Trades!$O3:$O$905,0)),Trades!H:H)}
C5	{=INDEX(Trades!H4:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O5)),1),"")),Trades!$O4:$O$905,0)),Trades!H:H)}
C6	{=INDEX(Trades!H5:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O6)),1),"")),Trades!$O5:$O$905,0)),Trades!H:H)}
C7	{=INDEX(Trades!H6:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O7)),1),"")),Trades!$O6:$O$905,0)),Trades!H:H)}
D2	{=INDEX(Trades!I1:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O2)),1),"")),Trades!$O1:$O$905,0)),Trades!I:I)}
D3	{=INDEX(Trades!I2:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O3)),1),"")),Trades!$O2:$O$905,0)),Trades!I:I)}
D4	{=INDEX(Trades!I3:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O4)),1),"")),Trades!$O3:$O$905,0)),Trades!I:I)}
D5	{=INDEX(Trades!I4:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O5)),1),"")),Trades!$O4:$O$905,0)),Trades!I:I)}
D6	{=INDEX(Trades!I5:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O6)),1),"")),Trades!$O5:$O$905,0)),Trades!I:I)}
D7	{=INDEX(Trades!I6:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O7)),1),"")),Trades!$O6:$O$905,0)),Trades!I:I)}
E2	{=INDEX(Trades!O1:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O2)),1),"")),Trades!$O1:$O$905,0)),Trades!O:O)}
E3	{=INDEX(Trades!O2:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O3)),1),"")),Trades!$O2:$O$905,0)),Trades!O:O)}
E4	{=INDEX(Trades!O3:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O4)),1),"")),Trades!$O3:$O$905,0)),Trades!O:O)}
E5	{=INDEX(Trades!O4:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O5)),1),"")),Trades!$O4:$O$905,0)),Trades!O:O)}
E6	{=INDEX(Trades!O5:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O6)),1),"")),Trades!$O5:$O$905,0)),Trades!O:O)}
E7	{=INDEX(Trades!O6:$O$905,(MATCH((IFERROR(INDEX(Trades!$O$1:$O$905,SMALL(IF((Trades!$O$1:$O$905<>""),ROW(Trades!$O$1:$O$905)),ROW(Trades!$O7)),1),"")),Trades!$O6:$O$905,0)),Trades!O:O)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Special-K99]

Try

INDIRECT(A2&A4&":"&A3&A5)

INDIRECT() is slow and people advise to steer clear of it though sometimes it's the only option.

 

[viktor_david]

Thanks a lot, sir.. Live saver
I appreciate it