Vlookup problem

[mariomaf]

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...

 

[Bob Phillips]

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]

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

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

I got the regular version and made the array paste.

What I think might cause the issue is that the formula determines hi
current position from the top, when I put the formula on the top firs
row, it works fine. But when I paste it on the 10th cell it gaves n
result and not even an error..

 

[Bob Phillips]

Do you mean that you tried to put the formula in say rows 10-20 and it did
not work, or that the data is in rows 10 down.

I just tried with the formula in rows 11-20, and it worked fine.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mariomaf]

I did as well as formula as well as data!

 

[Bob Phillips]

Want to send it to me?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mariomaf]

That is oke... but you are not registered... and I won't attach it i
the forum here.... will do when all is finished..

 

[Bob Phillips]

What does registered mean?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)