FIND THE LOWEST NUMBER IN AN ARRAY
Posted by Lars on June 12, 2001 11:25 AM
Hey Mark if you are still there I need your help again!
Here is my data:
column A B C
ATLFLLDLQ 1 50 ATLFLLUSQ
ATLFLLUAK 4 80
ATLFLLUAK 4 85
ATLFLLUSQ 2 75
ANCFLLHPV 1 50
ANCFLLNWT 2 75
Can I lookup the ATLFLLUSQ on the right column above (which is on another sheet) and look it up in a range (represented by the first three columns above)and find all the ones that start with the FIRST 6 DIGITS ATLFLL (ignoring the last three digits) in the left column and within all the ATLFLL's that are found, find the one that has number 1 (column B)next to it and put the last character in a cell and also put the fare (column c above)in another cell and the carrier (7 and 8th digit) in another cell?
For example find ATLFLLUSQ (in column A look only for ATLFLL that has the 1 next to it and return in three different cells the carrier (7th & 8th character in the string) the last character and the fare (column C)- the answer would be DL Q 50.
Is this possible?
Posted by Mark W. on June 12, 2001 12:06 PM
Can't do it with 1 formula unless you include
columns in your table for "DL" and "Q".
Posted by IML on June 12, 2001 12:39 PM
I'll, of course, defer to the master. But is there no hope of manipulating the text?
You can find the lowest fare with something like:
=SUM((LEFT(A1,6)=LEFT(Sheet1!A1:A6,6))*(Sheet1!B1:B6=1)*(Sheet1!C1:C6)
Could some crazy index match with text manipulation work?
Posted by Mark W. on June 12, 2001 12:48 PM
Yep, but not with 1 formula.
Posted by lars on June 12, 2001 1:02 PM
Hoe do I manipulate it for it to work?
Posted by Mark W. on June 12, 2001 1:20 PM
Re: Hoe do I manipulate it for it to work?
If you ad the following data into cells A1:E6...
{"ATLFLLDLQ","DL","Q",1,50
;"ATLFLLUAK","UA","K",4,80
;"ATLFLLUAK","UA","K",4,85
;"ATLFLLUSQ","US","Q",2,75
;"ANCFLLHPV","HP","V",1,50
;"ANCFLLNWT","NW","T",2,75}
Note: Column B data created using =LEFT(RIGHT(A1,3),2)
and column C data created using =RIGHT(A1,1).
...then, the entry of the array formula...
{=INDEX($A$1:$E$6,MAX((LEFT("ATLFLLUSQ",6)=LEFT($A$1:$A$6,6))*($D$1:$D$6=1)*ROW($A$1:$A$6)),{2,3,5})}
...into cell selection, G1:I1, might do the trick!
Posted by lars Thanks How do I not show blank info? on June 12, 2001 1:48 PM
almost there
The ATLFLL is 100 rows down but the formula is pulling the first one at the top
: Yep, but not with 1 formula.
Posted by IML on June 12, 2001 1:53 PM
vlookup route
This should work except in cases where you have two #1 for a fair, then it will pull the first one.
Next to your data (sheet1) in column D put:
=LEFT(A1,6)&"-"&B1
and column E put:
=MID(A1,7,2)&" "&RIGHT(A1,1)&" $"&C1
You can hid these columns if you like.
On your new sheet put the formula
=VLOOKUP(LEFT(A1,6)&"-1",Sheet1!D1:E6,2,FALSE)
where your put the full city name in A1 on the same sheet.
Posted by lars on June 12, 2001 2:36 PM
That one worked!
Next to your data (sheet1) in column D put:
: Yep, but not with 1 formula.
Posted by Mark W. on June 12, 2001 2:56 PM
Re: almost there
In your sample data ATLFLL is the 1st row. Also,
this is an array function. Did you enter it using
the Control+Shift+Enter key combination? The ATLFLL is 100 rows down but the formula is pulling the first one at the top If you ad the following data into cells A1:E6... : {"ATLFLLDLQ","DL","Q",1,50 ;"ATLFLLUAK","UA","K",4,80 ;"ATLFLLUAK","UA","K",4,85 ;"ATLFLLUSQ","US","Q",2,75 ;"ANCFLLHPV","HP","V",1,50 ;"ANCFLLNWT","NW","T",2,75} : Note: Column B data created using =LEFT(RIGHT(A1,3),2) and column C data created using =RIGHT(A1,1). : ...then, the entry of the array formula... : {=INDEX($A$1:$E$6,MAX((LEFT("ATLFLLUSQ",6)=LEFT($A$1:$A$6,6))*($D$1:$D$6=1)*ROW($A$1:$A$6)),{2,3,5})} : ...into cell selection, G1:I1, might do the trick! :
Posted by Lars on June 12, 2001 5:20 PM
Re: almost there
Yea I put it in the first row to make it easier, I did enter it as an array and it still didn't work. It is reading the top row and bringing that info over..I think I need some type of Match formula for it to work.. Thanks for your help!
In your sample data ATLFLL is the 1st row. Also,
Posted by Mark W. on June 13, 2001 6:24 AM
Re: almost there
Works fine for me regardless of how the sample
data is sorted. Are you sure all of the cell
ranges were properly adjusted for your data? Yea I put it in the first row to make it easier, I did enter it as an array and it still didn't work. It is reading the top row and bringing that info over..I think I need some type of Match formula for it to work.. Thanks for your help!
