software

Can I get VLookup to ignore certain data.

My situation is this,
Sheet 1 has
ColumnA Column B
Name Job#
Bob 1234
Bob 1235
Bob 1236

On Sheet 2 I have a combo box so I can pick any name from column A on sheet
one, then I want to use Vlookup, or whatever will work, to show me the Job
numbers that a selected person has worked on. So when I pick quot;bobquot; from my
combo box in A1, B1:B3 would display 1234, 1235, 1236.
Can anyone help?
One way ..

In Sheet2, assume cell A1 will contain the selection's result, eg: Bob

Put in A2:
=IF(ISERROR(SMALL(B:B,ROW(A1))),quot;quot;,
INDEX(Sheet1!B:B,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(Sheet1!A2=quot;quot;,quot;quot;,IF(Sheet1!A2=$A$1,ROW(),quot;quot;))
(Leave B1 empty)

Select A2:B2, fill down to say, B10,
to cover the max expected returns for any name in A1

A2:A10 will return the required results for the name in A1,
all neatly bunched at the top
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;PH NEWSquot; gt; wrote in message
...
gt; Can I get VLookup to ignore certain data.
gt;
gt; My situation is this,
gt; Sheet 1 has
gt; ColumnA Column B
gt; Name Job#
gt; Bob 1234
gt; Bob 1235
gt; Bob 1236
gt;
gt; On Sheet 2 I have a combo box so I can pick any name from column A on
sheet
gt; one, then I want to use Vlookup, or whatever will work, to show me the Job
gt; numbers that a selected person has worked on. So when I pick quot;bobquot; from my
gt; combo box in A1, B1:B3 would display 1234, 1235, 1236.
gt; Can anyone help?
gt;
gt;
Absolutely fantastic Max, thank you very much

SPL

quot;Maxquot; gt; wrote in message
...
gt; One way ..
gt;
gt; In Sheet2, assume cell A1 will contain the selection's result, eg: Bob
gt;
gt; Put in A2:
gt; =IF(ISERROR(SMALL(B:B,ROW(A1))),quot;quot;,
gt; INDEX(Sheet1!B:B,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))
gt;
gt; Put in B2:
gt; =IF(Sheet1!A2=quot;quot;,quot;quot;,IF(Sheet1!A2=$A$1,ROW(),quot;quot;))
gt; (Leave B1 empty)
gt;
gt; Select A2:B2, fill down to say, B10,
gt; to cover the max expected returns for any name in A1
gt;
gt; A2:A10 will return the required results for the name in A1,
gt; all neatly bunched at the top
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Can I get VLookup to ignore certain data.
gt; gt;
gt; gt; My situation is this,
gt; gt; Sheet 1 has
gt; gt; ColumnA Column B
gt; gt; Name Job#
gt; gt; Bob 1234
gt; gt; Bob 1235
gt; gt; Bob 1236
gt; gt;
gt; gt; On Sheet 2 I have a combo box so I can pick any name from column A on
gt; sheet
gt; gt; one, then I want to use Vlookup, or whatever will work, to show me the
Job
gt; gt; numbers that a selected person has worked on. So when I pick quot;bobquot; from
my
gt; gt; combo box in A1, B1:B3 would display 1234, 1235, 1236.
gt; gt; Can anyone help?
gt; gt;
gt; gt;
gt;
gt;
Delighted !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;PH NEWSquot; gt; wrote in message
...
gt; Absolutely fantastic Max, thank you very much
gt;
gt; SPL
I understand how most of that works, but how does it bunch all the numbers
at the top?

SPLquot;Maxquot; gt; wrote in message
...
gt; Delighted !
gt; Thanks for the feedback ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;PH NEWSquot; gt; wrote in message
gt; ...
gt; gt; Absolutely fantastic Max, thank you very much
gt; gt;
gt; gt; SPL
gt;
gt;
quot;PH NEWSquot; wrote
gt; I understand how most of that works,
gt; but how does it bunch all the numbers at the top?

Via the part: SMALL(B:B,ROW(A1)) within the MATCH(...)
With the ROW(A1) acting as the incrementer: 1,2,3, ... as we copy down from
the starting cell, the expression SMALL(B:B,ROW(A1)) returns the smallest
number in col B (where we have the criteria to assign arbitrary row numbers
for lines which satisfy), then the 2nd smallest number, the 3rd smallest,
and so on, until all the numbers in col B are exhausted.
(This essentially produces the desired quot;bunching at the topquot; effect)

The INDEX(Sheet1!B:B,MATCH(...)) then translates the expressions' returns
accordingly to the final outputs which are the job#s in Sheet1's col B.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Once again, thank you Max, much obliged, this has really helped me out and I
always feel better if I know how something is working.

Cheers,

SPL

quot;Maxquot; gt; wrote in message
...
gt; quot;PH NEWSquot; wrote
gt; gt; I understand how most of that works,
gt; gt; but how does it bunch all the numbers at the top?
gt;
gt; Via the part: SMALL(B:B,ROW(A1)) within the MATCH(...)
gt; With the ROW(A1) acting as the incrementer: 1,2,3, ... as we copy down
from
gt; the starting cell, the expression SMALL(B:B,ROW(A1)) returns the smallest
gt; number in col B (where we have the criteria to assign arbitrary row
numbers
gt; for lines which satisfy), then the 2nd smallest number, the 3rd smallest,
gt; and so on, until all the numbers in col B are exhausted.
gt; (This essentially produces the desired quot;bunching at the topquot; effect)
gt;
gt; The INDEX(Sheet1!B:B,MATCH(...)) then translates the expressions' returns
gt; accordingly to the final outputs which are the job#s in Sheet1's col B.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;
You're welcome !
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;PH NEWSquot; gt; wrote in message
...
gt; Once again, thank you Max, much obliged, this has really helped me out and
I
gt; always feel better if I know how something is working.
gt;
gt; Cheers,
gt;
gt; SPL