Lookup with multiple results

[Guest]

Is there any way to use lookup formulas to have Excel
find ALL of the values associated with the lookup
parameter and somehow list them out? Here's an example:

A B
Part No. Work Order
1 123 8000
2 123 8001
3 456 8002
4 789 8003

I want to know ALL of the production work orders (col. B)
that are open for a given product (col. A). Is there a
way to do a lookup on part no. 123 (A1) where Excel would
return both of the open work orders 8000 and 8001?

Thanks.

 

[Peo Sjoblom]

This is why autofilter exist. Try that or use advanced filter and copy
somewhere else to create sub lists
There are ways of doing it with formulas (somewhat complicated) but it is
far better to use a filter

 

[RagDyer]

I posted a suggested solution to this yesterday, at least I thought I did.

I'm copying it below, just in case you don't know how to use your news
reader, and can't tell if you received any answers.

If you did see this yesterday, and for some reason, either didn't like it,
or didn't understand it, it would have been nice of you to post back with
any reservations that you might have had.
Just plain user-group etiquette.

Four other people beside myself posted replies to yesterday's query, and
today, with Peo's, which duplicated one of yesterday's suggestions, the
total is 6.
Part Num in Column A
Work Order in Column B
Labels in Row 1

Part Number to look up in C1

Data in A2:B100

Enter this formula in C2:

=INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW(A2:A100)-1))

Now, you have to approximate how many times the part number will appear in
Column A, and then add extra rows of this formula, in order to insure that
all occurrences are returned.

Say that you could have 10 occurrences.
Then drag and copy this formula down for 20 rows, hit F2, and then do
CSE (<Ctrl> <Shift> <Enter>) to make this an array formula.

If it's done correctly, the formulas will automatically be enclosed in curly
brackets.

If there are only 8 part numbers to match C1, the additional rows containing
your formula will return a #NUM! error.

If no errors are returned, you should extend your formula rows in order to
insure that all the part numbers are returned.

This is why you will *always* want to see at least one error !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Is there any way to lookup multiple answers to a lookup formula. Normally,
Excel will stop at the first match it finds and return that as the answer
when, in fact, there may be more than one answer. I want to find the other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000 and
6010. I want to do a lookup of Part No. 123 and find ALL the work orders
associated with it (listed separately, of course). Is there any way to do
this in Excel? Thanks


Is there any way to use lookup formulas to have Excel
find ALL of the values associated with the lookup
parameter and somehow list them out? Here's an example:

A B
Part No. Work Order
1 123 8000
2 123 8001
3 456 8002
4 789 8003

I want to know ALL of the production work orders (col. B)
that are open for a given product (col. A). Is there a
way to do a lookup on part no. 123 (A1) where Excel would
return both of the open work orders 8000 and 8001?

Thanks.

 

[Bill]

First to the newsgroup technicalities: When I look at
the thread, I see only TWO responses - yours and Peo's.
Until yesterday, it was only Peo's. Why don't I see what
you see. As for the news reader, I don't even know what
that is, so you're right, I guess I don't know how to use
it. I just refresh the newsgroup periodically to see if
there has been a response.

As to your suggestion, this looks like a good approach.
I will try this out today and let you know how it works.
Thanks for ALL suggestions (including the ones I
apparently haven't yet figured out how to view!).

 

[RagDyer]

I was not talking about this thread Bill, but the one posted the day before
which is attached to the bottom of my suggested solution.

Anyway ... here are several links that you might find helpful in your use of
the newsgroups.

Grab a pot of coffee, and spend some time surfing them.

http://www.cpearson.com/excel/newposte.htm

http://www.mvps.org/dmcritchie/excel/xlnews.htm

http://www.mvps.org/dmcritchie/excel/oe6.htm

http://www.cpearson.com/excel/DirectConnect.htm

http://www.cpearson.com/excel/links.htm#Newsgroups

http://www.mvps.org/dmcritchie/excel/posting.htm
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------



First to the newsgroup technicalities: When I look at
the thread, I see only TWO responses - yours and Peo's.
Until yesterday, it was only Peo's. Why don't I see what
you see. As for the news reader, I don't even know what
that is, so you're right, I guess I don't know how to use
it. I just refresh the newsgroup periodically to see if
there has been a response.

As to your suggestion, this looks like a good approach.
I will try this out today and let you know how it works.
Thanks for ALL suggestions (including the ones I
apparently haven't yet figured out how to view!).

 

[Bill]

I'm slowly catching on... I didn't realize that my
original question turned into two threads (nor do I
understand why it did), so I've only been refreshing the
latest. That explains why I never saw all the responses
(now I've found them). So thanks for your patience - and
for the links. I'll definitely take a look.

One thing puzzles me about the responses: a number of
people suggest using Autofilter, but that doesn't really
help. Filtering would be fine if I wanted to just pick a
part number and see the associated work orders. But what
I want is a table that lists ALL part numbers and ALL of
the work orders that are associated with each of them.

Two suggestions look very promising (both from another
newsgroup) - I'm about to try yours. Stay tuned....

Bill

 

[RagDyer]

<<<"But what I want is a table that lists ALL part numbers and ALL of
the work orders that are associated with each of them.">>>

My suggestion will *not* do this !
It will list the work orders *only* for the part number entered in C1.

Perhaps you really want to use a Pivot Table.
I'm not too familiar with them, but you should try Debra's web site.

http://www.contextures.com/tiptech.html

And scroll down to Pivot Tables.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I'm slowly catching on... I didn't realize that my
original question turned into two threads (nor do I
understand why it did), so I've only been refreshing the
latest. That explains why I never saw all the responses
(now I've found them). So thanks for your patience - and
for the links. I'll definitely take a look.

One thing puzzles me about the responses: a number of
people suggest using Autofilter, but that doesn't really
help. Filtering would be fine if I wanted to just pick a
part number and see the associated work orders. But what
I want is a table that lists ALL part numbers and ALL of
the work orders that are associated with each of them.

Two suggestions look very promising (both from another
newsgroup) - I'm about to try yours. Stay tuned....

Bill

 

[Guest]

I already tried a pivot table. Unfortunately, that
doesn't work either because Excel adds together any
multiple occurrences (they ARE, in fact, numbers, so it's
logical. If only there were a way to just return the raw
value without performing any operations, this would be
the perfect solution.....) So two work order numbers
8000 and 8001 (both tied to the same part number on the
same production date) shows up in the pivot table as
16001.

A B C
P/N Wrk Ord.No. Production Date
1 123 8000 1/26/04
2 456 8005 2/2/04
3 789 8007 2/2/04
4 123 8001 1/26/04

Here's what the pivot table would look like:
Production Date
P/N 1/26/04 2/2/04
123 16001
456 8005
789 8007

 

[RagDyer]

Have you tried formatting your Work Order column as Text, and *then*
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
start with the Pivot Table ?

I already tried a pivot table. Unfortunately, that
doesn't work either because Excel adds together any
multiple occurrences (they ARE, in fact, numbers, so it's
logical. If only there were a way to just return the raw
value without performing any operations, this would be
the perfect solution.....) So two work order numbers
8000 and 8001 (both tied to the same part number on the
same production date) shows up in the pivot table as
16001.

A B C
P/N Wrk Ord.No. Production Date
1 123 8000 1/26/04
2 456 8005 2/2/04
3 789 8007 2/2/04
4 123 8001 1/26/04

Here's what the pivot table would look like:
Production Date
P/N 1/26/04 2/2/04
123 16001
456 8005
789 8007

 

[Bill]

This is an excellent suggestion and would be the simplest
solution of all - if it worked. I just tested this,
however, and it seems that Excel doesn't like text in
pivot tables. Even after reformatting the work order
numbers to text, Excel still treated them like numbers
(and added duplicates together). So then I tried using
letters to represent work orders instead of numbers. The
pivot table returned zeroes, so the letters were
apparently just ignored. Keep thinking!

 

[Ragdyer]

As I said before, I don't know much about Pivot Tables, but I know there is
an option to double click on the data label so that you can change the
manner in which it is presented.
When you drag Part Number into the data field, doesn't it say SUM ?
Double click on this and play around with the different options.

With all my inexperience, I was able to list out your part numbers without
them being totaled, and they were listed next to the Work orders also.

This is definitely the way you want to go (Pivot Tables).

 

[Bill]

I've worked with pivot tables quite a bit and I can't get
this to work. You can manipulate the data as far as
having it count, sum, take the product etc. But it does
not seem to work with non-numbers. I used ABC, DEF, etc.
as work order "numbers" and the pivot table shows 1's if
you select "count" and zeroes if you select "sum". None
of the other options helps either. Finally, you cannot
drag data within a pivot table unless you first convert
the table via copy/paste special/values to a non-
calculated table. So I'm not sure what you mean about
dragging. You can manipulate the source data but not the
pivot table itself (other than formatting and the other
built-in options). Too bad, because you're right: the
pivot table would be the way to go.....

 

[Peo Sjoblom]

Obviously you can only count text entries but if you drag the header with
the text entries to both the row and to the
data you'll get a count

 

[Ragdyer]

Me advising someone on Pivot Tables is tantamount to the blind leading the
blind, BUT, I repeat:
"With all my inexperience, I was able to list out your part numbers without
them being totaled, and they were listed next to the Work orders also."

I don't know if the display is good enough, but the data was there.
Here is what I did.

Set up test database:

Labels in Row1
Part Num in Column A
Work Order in Column B
Data in A2:B20

Selected A1:B20 then,
<Data> <PivotTableReport>

Step 1 = Microsoft Excel list <Next>

Step 2 = A1:B20 already selected <Next>

Step 3 = Drag "PartNo" field button into Row portion of diagram,
Drag "Work Ord" field button into Column portion *and* into
Data portion of diagram, <Next>

Step 4 = Existing Worksheet - then click in cell D1, <Finish>

A list is now displayed, with the part numbers going down Column D.
Only unique numbers are listed, (6 of them),even though there were 19
entries, no duplicates listed.

Going across the columns, in the same row as the particular part number, are
the work orders associated with that part number.

Now, it may not be pretty, but it at least demonstrates that if I can do
this with a Pivot Table and get these results, the proper utilization of
this feature should return any and all the information that you might
desire.

 

[Bill]

Well you're better than you give yourself credit for. I
just tried this and it worked as advertised. I then
tried it with letters instead of numbers for the work
orders (just for fun) and that really does NOT work - as
I've said before, it seems that the data must be numbers
to be returned properly. So your idea works great for me
because the work order numbers are, in fact, numbers.
Thanks for your persistence!

 

[RagDyer]

Glad to help ... BUT,

Beside using that pot of coffee to help reading up on newsgroups,
Grab another one and spend time at Debra's web site, re: Pivot Tables

I previously gave you the link.
Here it is again:

http://www.contextures.com/tiptech.html
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Well you're better than you give yourself credit for. I
just tried this and it worked as advertised. I then
tried it with letters instead of numbers for the work
orders (just for fun) and that really does NOT work - as
I've said before, it seems that the data must be numbers
to be returned properly. So your idea works great for me
because the work order numbers are, in fact, numbers.
Thanks for your persistence!