Vlookup problem

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 :-(

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

I got the regular version and made the array paste.

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> I got the regular version and made the array paste.
>
> What I think might cause the issue is that the formula determines his
> current position from the top, when I put the formula on the top first
> row, it works fine. But when I paste it on the 10th cell it gaves no
> result and not even an error...
>
>
> --
> mariomaf
> ------------------------------------------------------------------------
> mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
> View this thread: http://www.excelforum.com/showthread...hreadid=497836
>

I did as well as formula as well as data!

Want to send it to me?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mariomaf" <[email protected]> wrote in
message news:[email protected]...
>
> I did as well as formula as well as data!
>
>
> --
> mariomaf
> ------------------------------------------------------------------------
> mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
> View this thread: http://www.excelforum.com/showthread...hreadid=497836
>

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

What does registered mean?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mariomaf" <[email protected]> wrote in
message news:[email protected]...
>
> That is oke... but you are not registered... and I won't attach it in
> the forum here.... will do when all is finished...
>
>
> --
> mariomaf
> ------------------------------------------------------------------------
> mariomaf's Profile:
http://www.excelforum.com/member.php...o&userid=30098
> View this thread: http://www.excelforum.com/showthread...hreadid=497836
>