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

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

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

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

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

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

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!