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.

 

[viktor_david]

Is there someone who can help me with this matter, please?

 

[Special-K99]

The end of all the formulas is not being displayed correctly.
Retype one of the formulas as text so we can see the formula exactly.

Also your description is incorrect.
The Securitys should be 88 90 and 99, not 88 89 and 90.

 

[viktor_david]

The end of all the formulas is not being displayed correctly.
Retype one of the formulas as text so we can see the formula exactly.

Also your description is incorrect.
The Securitys should be 88 90 and 99, not 88 89 and 90.

Special-K99, thanks for answering..
Here I will send the formula as text. However, I do get the whole formula if i scroll a bit to right:
{=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)}

You are totally correct about the security numbers, but that is the problem I am trying to figure out.. the square on the top left shows the results after plotting the formula... They shouwld show 88, 90, and 99. Could you take a look at the formula... If you do not get to see the formula, I can post an image instead. Please, let me know

 

[Special-K99]

I can see the formula now thanks.
Try (untested)

in A2
=IFERROR(INDEX($O$1:$A$10000,SMALL(IF(($O$1:$O$10000<>""),ROW($F$9:$O$10000)),ROW(A1)),COLUMN()),"")
Array formula, use Ctrl-Shift-Enter
copy down column A
and then copy column A as far right as column E

 

[viktor_david]

Brother.. you just solved that problem.. niceeee... super excited

Now I have something else... I will attach 2 pictures
Picture 1 - The column Qty (E) doesn't show the data from column O, like the quantity for that customer, it shows 0

Picture 2 - If I copy column O and paste it on column J, i do get the result...
I tried changing the position of row from E2 to O2 or even H4 but it is still refereing to column J.

Do you have any idea?

 

[Special-K99]

in column E
change the COLUMN() part at the end of the formula to 15
and copy down the column
Remember this is an array formula
Array formula, use Ctrl-Shift-Enter

 

[viktor_david]

in column E
change the COLUMN() part at the end of the formula to 15
and copy down the column
Remember this is an array formula
Array formula, use Ctrl-Shift-Enter

Hi,

Could you elaborate a bit more on this? since i delete COLUMN() and write 15 and error. Or i write COLUMN(15) and nothing happens.

Thanks a lot again

 

[Special-K99]

Oops! It wo'nt be 15 since we're counting from column F.
Replace the 15 with 10

 

[viktor_david]

Oops! It wo'nt be 15 since we're counting from column F.
Replace the 15 with 10

Thank you very much. I am really speechless
You help me solve this case...

By any chance you know how to do the following?
I wan to include in a formula a range from, eg, T1:V100. But i want to be able to select T with another cell as referance and the 1 and the 100 with another cell... so basically I write: A2=T, A3=V, A4=1, A5=100. and then, somehow i write a formula that selects that range.. so If i chance A2 to F then the range is F1:V100, for example
Do you know?