After I have been searching through the whole forum I think I still have
something unwritten here.

It regards the following:

I have a security order list. This consist of several columns most
important are

Column A Column B

Sec a 15,00
Sec b 14,50
Sec c 16,00
Sec a 15,10
Sec a 15,70
Sec b 16,70
Sec a 14,80

The first column gives the name of the security, the second column
gives the bid price.

What I want is that I want to sort out all bid prices for a specific
security. Vlookup works fine but again it just gives the first record.
How can I get all bid prices for security a on a new worksheet without
blank cells. If possible I would like to have it sorted as well.

Please help me out here...


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile: http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836

Select a range of cells on the new worksheet, and in the formula bar enter

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$20="Sec
a",ROW($A1:$A20),""),ROW($A1:$A20))),"",
INDEX(Sheet1!$B$1:$B$20,SMALL(IF(Sheet1!$A$1:$A$20 ="Sec
a",ROW($A1:$A20),""),ROW($A1:$A20))))

this is an array formula, so it nbeeds to be committed with
Ctrl-Shift-Enter.

Not sorted though

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mariomaf" wrote in
message ...

After I have been searching through the whole forum I think I still have
something unwritten here.

It regards the following:

I have a security order list. This consist of several columns most
important are

Column A Column B

Sec a 15,00
Sec b 14,50
Sec c 16,00
Sec a 15,10
Sec a 15,70
Sec b 16,70
Sec a 14,80

The first column gives the name of the security, the second column
gives the bid price.

What I want is that I want to sort out all bid prices for a specific
security. Vlookup works fine but again it just gives the first record.
How can I get all bid prices for security a on a new worksheet without
blank cells. If possible I would like to have it sorted as well.

Please help me out here...


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836

Wonderful!! Good solution. I was already developing some VB script which
I better understand myself.

Nevertheless the script seems not to work when I don't start in the
first row? Is there something I can do about it. Now I can't use column
headers :-(


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile: http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836

Do you mean my formula doesn't work in that instance, or your code? If the
latter, post the code.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mariomaf" wrote in
message ...

Wonderful!! Good solution. I was already developing some VB script which
I better understand myself.

Nevertheless the script seems not to work when I don't start in the
first row? Is there something I can do about it. Now I can't use column
headers :-(


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836

This is my code:

=IF(ISERROR(SMALL(IF('Uitgevoerde en geplste.
orders'!$K$5:$K$13="b";ROW($K5:$K13);"");ROW($K5:$ K13)));"";INDEX('Uitgevoerde
en geplste. orders'!$L$5:$L$13;SMALL(IF('Uitgevoerde en geplste.
orders'!$K$5:$K$13 ="b";ROW($K5:$K13);"");ROW($K5:$K13))))

It leaves my fields empty... but gives no error


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile: http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836

Did you array enter, Ctrl-Shift-Enter?

Also, do you have a language version of Excel, if so, which?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mariomaf" wrote in
message ...

This is my code:

=IF(ISERROR(SMALL(IF('Uitgevoerde en geplste.

orders'!$K$5:$K$13="b";ROW($K5:$K13);"");ROW($K5:$ K13)));"";INDEX('Uitgevoer
de
en geplste. orders'!$L$5:$L$13;SMALL(IF('Uitgevoerde en geplste.
orders'!$K$5:$K$13 ="b";ROW($K5:$K13);"");ROW($K5:$K13))))

It leaves my fields empty... but gives no error


--
mariomaf
------------------------------------------------------------------------
mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
View this thread: http://www.excelforum.com/showthread...hreadid=497836