Formula doesn't work as I want it

V

viktor_david

New Member
Joined
Mar 16, 2017
Messages
29
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
ABCDEFGHIJKLMNOPQR
1DateB/SQty. TotTickerQty.10bp
202.05.2017SELL97290Security84109610bp + fee
303.05.2017BUY38370Security852865bp
406.05.2017SELL2164.97Security881555bp +fee
506.05.2017SELL2164.97Security88155Special
606.05.2017SELL2164.97Security88155
7#I/T#I/T#I/T#I/T#I/T
8DateK/SantalltickerCust. 1Cust. 2Cust. 3Cust. 4Cust. 5
929.04.2017SELL532Security81,532,
1030.04.2017SELL16361Security82,1,622,
1101.05.2017SELL66378Security83,5,011,,2,836,
1202.05.2017SELL97290Security84,7,263,,1,096,,4,109,
1303.05.2017BUY38370Security85,2,308,,286,,1,625,
1404.05.2017SELL7500Security86
1505.05.2017SELL1063.49Security87
1606.05.2017SELL2164.97Security88,155,
1707.05.2017SELL1159.23Security89
1808.05.2017BUY4800Security90,155,
1909.05.2017BUY300Security91
2010.05.2017BUY150Security92,150,
2111.05.2017BUY3200Security93
2212.05.2017BUY1500Security94
2313.05.2017SELL317Security95
2414.05.2017SELL300Security96
2515.05.2017SELL5000Security97
2616.05.2017BUY13900Security98
2717.05.2017BUY10760Security99,155,
2818.05.2017BUY2000Security100
2919.05.2017BUY26Security101
Trades
Cell Formulas
RangeFormula
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.
 
Sort by date Sort by votes
Try

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

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

E
  • Solved
Formula not working
Replies
2
Views
299
esmarques
E
mohsinbipu
  • Solved
Count the occurrence with the array formula
Replies
2
Views
488
Fluff
Fluff
J
  • Solved
Sum values in column O until empty cell is reached whilst excluding "NO" values
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs
MrDB4Excel
  • Question Question
Cell Address Hyperlink
Replies
5
Views
564
MrDB4Excel
MrDB4Excel
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
260
RobP
R

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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