min and if

[jam]

Hi
Anyone can solve this problem
I have 2 rows
a b c d e f
46 55 66 46 55 56
I used min fuction it gives me the first 46
I want to to hvae the 2 46 with the names a and d
If can not solve this since it has one criterion
Thanks for any help
Jam

 

[Guest]

I suggest that you transpose your data from rows into columns. Filtering can
be applied to column data, but not row data.

In column form, you can just turn on Autofilter and select the 46 values.

 

[Biff]

Hi!

Assume this data is in the range A1:F2

a b c d e f
46 55 66 46 55 56


Enter this formulas as an array using the key combination of
CTRL,SHIFT,ENTER in cell A5 and copy across to F5:

=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),INDEX($A1:$F1,SMALL(IF($A2:$F2=MIN($A2:$F2),COLUMN($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),"")

Enter this formula in A6 and copy across to F6:

=IF(A5="","",INDEX($A2:$F2,MATCH(A5,$A1:$F1,0)))

The results will be:

...........A..........B..........C..........D..........E..........F..........
5........a...........d............................................................
6.......46.........46...........................................................

Biff

 

[jam]

Hi Biff
Thanks for your reply
Up to now it still give me an error in formula in $F2,MIN, I do not
know why?
Itrierd to put the foemula in {} it gives error yoo
Thanks for help

 

[Biff]

Post the *EXACT* formula you tried along with the data involved.

Biff

 

[jam]

Hi Biff
I did exactly the same as you suggested
I put in a new worksheet from a1 to f1 abcdef, and in a2to f2 the
numbers
I copied the same formula that you posted
Still give error
Any help
Thanks
Jam

 

[jam]

Hi Biff
I did exactly the same as you suggested
I put in a new worksheet from a1 to f1 abcdef, and in a2to f2 the
numbers
I copied the same formula that you posted
Still give error
Any help
Thanks
Jam