Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
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

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)),I NDEX($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

Works fine for me, did you array-enter it? That is commit with
Ctrl-Shift-Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"jam" wrote in message
oups.com...
Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
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

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)),I NDEX($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

Hi!

I copied the array formulae and paste it , it gives error

What kind of error?

Copying and pasting an array formula will not make it an array formula. You
MUST use the specific key combination to make it an array formula. Select
the first cell where this formula is entered. Press function key F2. This
will put you in EDIT mode. Now, hold down both the CTRL key and the SHIFT
key. While holding down both of those keys hit the ENTER key. If done
properly Excel will enclose the formula (look at it in the formula bar) in
squiggly braces { }. You cannot just type these braces in. You MUST use the
key combination to produce them. Also, any time you EDIT an array formula
(like you just did), the formula MUST be re-entered as an array.

If you're still having problems post the *EXACT* formula you are using.

Biff

"jam" wrote in message
oups.com...
Hi
I posted this message, I got the next answer, I copied the array
formulae and paste it , it gives error, I enter it by typing still give
error
Any help?
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

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)),I NDEX($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

Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUM N($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam

Ok, I think I know what the problem is.........Google Groups!

It looks like you're posting through Google Groups.

Ever since they changed to this new version there have been problems
reported that when messages are posted some "junk" gets inserted into the
message. "Junk" refers to characters that were not typed by the poster.

See this screencap:

http://img518.imageshack.us/img518/8...problem6qw.jpg

These 2 formulas were copied from both of your posts in this thread and then
pasted into a worksheet. Notice the highlighted sections. After $A2 you see
those dashes (-), well, those aren't supposed to be there and that is some
of the "junk" that I was referring to. I use OE to view this group and I
don't see those dashes in any of the posts but they are there as evidenced
by them appearing when the formulas are pasted into a worksheet.

So, remove all the "junk", re-enter the fomula as an array and it should
work.

Biff

"jam" wrote in message
oups.com...
Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUM N($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam

Ok, my suspicions are confirmed.

I just viewed this thread in Google Groups and sure enough, those dashes
show up there.

I did not type in those dashes. They are not part of the formula. They're
Google "junk"!

Biff

"Biff" wrote in message
...
Ok, I think I know what the problem is.........Google Groups!

It looks like you're posting through Google Groups.

Ever since they changed to this new version there have been problems
reported that when messages are posted some "junk" gets inserted into the
message. "Junk" refers to characters that were not typed by the poster.

See this screencap:

http://img518.imageshack.us/img518/8...problem6qw.jpg

These 2 formulas were copied from both of your posts in this thread and
then pasted into a worksheet. Notice the highlighted sections. After $A2
you see those dashes (-), well, those aren't supposed to be there and that
is some of the "junk" that I was referring to. I use OE to view this group
and I don't see those dashes in any of the posts but they are there as
evidenced by them appearing when the formulas are pasted into a worksheet.

So, remove all the "junk", re-enter the fomula as an array and it should
work.

Biff

"jam" wrote in message
oups.com...
Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUM N($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam

Hi Biff
Thanks for your help, I think that there is not only junk things, but
that eats the brackets
I've counted the opened and closed brackets that doea not match, tried
to see where, could not, any hel?
Thanks
Jam
Biff wrote:
Ok, my suspicions are confirmed.

I just viewed this thread in Google Groups and sure enough, those dashes
show up there.

I did not type in those dashes. They are not part of the formula. They're
Google "junk"!

Biff

"Biff" wrote in message
...
Ok, I think I know what the problem is.........Google Groups!

It looks like you're posting through Google Groups.

Ever since they changed to this new version there have been problems
reported that when messages are posted some "junk" gets inserted into the
message. "Junk" refers to characters that were not typed by the poster.

See this screencap:

http://img518.imageshack.us/img518/8...problem6qw.jpg

These 2 formulas were copied from both of your posts in this thread and
then pasted into a worksheet. Notice the highlighted sections. After $A2
you see those dashes (-), well, those aren't supposed to be there and that
is some of the "junk" that I was referring to. I use OE to view this group
and I don't see those dashes in any of the posts but they are there as
evidenced by them appearing when the formulas are pasted into a worksheet.

So, remove all the "junk", re-enter the fomula as an array and it should
work.

Biff

"jam" wrote in message
oups.com...
Hi
Well I swear that I entered the formulae, and in edit mode, I hold down
the ctrt. shift, and enter keys, it hilights the columns and rows, but
it does not put the brackets{}, and then I get an error message
The formula is
=IF(COLUMNS($A:A)<=COUNTIF($A2:$F2,MIN($A2:$F2)),I NDEX($A1:$F1,SMALL(IF($A2**:$F2=MIN($A2:$F2),COLUM N($A1:$F1)-COLUMN($A:$A)+1),COLUMNS($A:A))),""),
the same you posted me, still give error, I have the impression that
the array formulae entering is not working, any help? I am using excel
2003
Jam