Possible array formula?

J

Jason

ID Side Size Type Price OCA Status
2345254 Buy 1 Lmt 103.3125 OCA Open
2345255 Sell 1 Lmt 103.34375
2345256 Sell 2 Lmt 103.5
2345257 Buy 2 Stop 103.5 OCA
2345258 Sell 2 Lmt 103.5
2345259 Buy 2 Stop 103.53125 OCA Open
2345260 Buy 2 Lmt 103.53125 OCA Open
2345261 Buy 1 RS 103.53125 OCA
2345262 Sell 1 RS 103.53125
2345263 Sell 2 RS 103.53125 Open
2345264 Sell 2 RS 103.625
2345265 Buy 3 Lmt 102.625
2345266 Buy 3 Lmt 103.28125 Open




Can anyone Please help?


With this data table how in excel can I find the record
with the lowest ID with a price of 103.53125 and a status of open?

Thanks in advance

Jason
 
H

Harlan Grove

Jason said:
ID Side Size Type Price OCA Status
2345254 Buy 1 Lmt 103.3125 OCA Open
2345255 Sell 1 Lmt 103.34375
2345256 Sell 2 Lmt 103.5
2345257 Buy 2 Stop 103.5 OCA
2345258 Sell 2 Lmt 103.5
2345259 Buy 2 Stop 103.53125 OCA Open
2345260 Buy 2 Lmt 103.53125 OCA Open
2345261 Buy 1 RS 103.53125 OCA
2345262 Sell 1 RS 103.53125
2345263 Sell 2 RS 103.53125 Open
2345264 Sell 2 RS 103.625
2345265 Buy 3 Lmt 102.625
2345266 Buy 3 Lmt 103.28125 Open ....
With this data table how in excel can I find the record
with the lowest ID with a price of 103.53125 and a status of open?
Click to expand...

If you data were in A1:G14, and if the table were sorted on ID in ascending
order already, the following array formula will give the ID number you seek.

=INDEX(A2:A14,MATCH(1,(E2:E14=A17)*(G2:G14=B17),0))

If the table weren't sorted on ID, try the array formula

=MIN(IF((E2:E14=A17)*(G2:G14=B17),A2:A14))
 
H

Harlan Grove

Harlan Grove said:
=INDEX(A2:A14,MATCH(1,(E2:E14=A17)*(G2:G14=B17),0)) ....
=MIN(IF((E2:E14=A17)*(G2:G14=B17),A2:A14))
Click to expand...

Oops! Replace A17 with 103.53125 and B17 with "Open".
 
M

MSP77079

I would use AutoFilter. Below is an example. Perhaps not the mos
elegant solution, but you should get the idea.

I took your table and pasted it into the range "B4:H17". Obviously yo
will not want this hard coded. If you have any problems getting thi
to be a variable range, post again.


If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
If ActiveSheet.AutoFilterMode = True The
ActiveSheet.AutoFilterMode = False

Range("B4:H17").Select
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="Open"
Selection.AutoFilter Field:=5, Criteria1:="103.53125"
LastRow = Range("B4").End(xlDown).Row
Set myRange = Range("B4:B" & LastRow)
Viz = 0
On Error Resume Next
Viz = myRange.SpecialCells(xlCellTypeVisible).Count
If Viz > 0 Then
For i = 5 To LastRow
If Cells(i, "B").EntireRow.Hidden = False Then
MsgBox "The item you seek is " & Cells(i, "B")
Exit Sub
End If
Next i
Else:
MsgBox "There were no records that matched your selectio
criteria."
End I
 
R

Ron Rosenfeld

ID Side Size Type Price OCA Status
2345254 Buy 1 Lmt 103.3125 OCA Open
2345255 Sell 1 Lmt 103.34375
2345256 Sell 2 Lmt 103.5
2345257 Buy 2 Stop 103.5 OCA
2345258 Sell 2 Lmt 103.5
2345259 Buy 2 Stop 103.53125 OCA Open
2345260 Buy 2 Lmt 103.53125 OCA Open
2345261 Buy 1 RS 103.53125 OCA
2345262 Sell 1 RS 103.53125
2345263 Sell 2 RS 103.53125 Open
2345264 Sell 2 RS 103.625
2345265 Buy 3 Lmt 102.625
2345266 Buy 3 Lmt 103.28125 Open




Can anyone Please help?


With this data table how in excel can I find the record
with the lowest ID with a price of 103.53125 and a status of open?

Thanks in advance

Jason
Click to expand...

Several Methods:

1. Use Autofilter and sort by ID.
2. Array formula: =MIN(IF(((Price=103.53125)*(Status="Open"))<>0,ID)) (Enter
by holding down <ctrl><shift> while hitting <enter>.

3. You can replace 103.53125 and "Open" by cell references.

4. You can also use the result of this formula to use conditional formatting
to highlight the row that contains the ID. For example, highlight your table,
A1:G14. Assume your formula is in L3. Format/Conditional Formatting Formula
Is: =$A1=$L$3 (Note the '$' signs). Format to taste.


--ron
 
K

kcc

Harlan Grove said:
If you data were in A1:G14, and if the table were sorted on ID in ascending
order already, the following array formula will give the ID number you seek.

=INDEX(A2:A14,MATCH(1,(E2:E14=A17)*(G2:G14=B17),0))

If the table weren't sorted on ID, try the array formula

=MIN(IF((E2:E14=A17)*(G2:G14=B17),A2:A14))
Click to expand...
Out of force of habit, I would use
=MIN(IF(E2:E14&G2:G14=A17&B17,A2:A14))
Mine has fewer comparisons to make, but I would imagine
the number to text conversion is inefficient.
For something this small, typing it in takes more time then
calc'ing it many, many times, so I wouldn't care. But I have
some spreadsheets that may have a few hundred array
formulas covering several thousand rows of data.
Are there any tests/studies of excel performance for various
functions and/or formula structure?
KenC
 
H

Harlan Grove

kcc said:
Out of force of habit, I would use
=MIN(IF(E2:E14&G2:G14=A17&B17,A2:A14)) ....
Are there any tests/studies of excel performance for various
functions and/or formula structure?
Click to expand...

Roll your own tests. It's not that hard.

Generally text operations take more time than arithmetic operations, and
concatenation is especially time consuming. Also, which this approach
probably wouldn't cause problems with the OP's data, it's not robust in
general. If both columns E and G were arbitrary text, there's the chance
that E#<>A17 and G#<>B17 but E#&G#=A17&B17. Once you add a concatenated
separator on each side of the comparison you're not saving much typing and
it's very unlikely you're doing anything other than wasting cycles.
 
Top