Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
"Jason" wrote...
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? 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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
"Harlan Grove" wrote...
.... =INDEX(A2:A14,MATCH(1,(E2:E14=A17)*(G2:G14=B17),0 )) .... =MIN(IF((E2:E14=A17)*(G2:G14=B17),A2:A14)) Oops! Replace A17 with 103.53125 and B17 with "Open". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
Harlan,
Rock on. You just saved me a few strands of hair. Jason "Harlan Grove" wrote in message ... "Jason" wrote... 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? 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)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
On Sat, 3 Jul 2004 20:49:10 -0500, "Jason" wrote:
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
"Harlan Grove" wrote in message
... "Jason" wrote... ID Side Size Type Price OCA Status 2345254 Buy 1 Lmt 103.3125 OCA Open .... 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? 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)) 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible array formula?
"kcc" wrote...
.... 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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |