A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup with multiple results



 
 
Thread Tools Display Modes
  #1  
Old January 23rd, 2004, 05:45 PM
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

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.
  #2  
Old January 23rd, 2004, 05:52 PM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

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

--

Regards,

Peo Sjoblom


wrote in message
...
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.



  #3  
Old January 24th, 2004, 04:29 AM
RagDyer
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

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!
==============================================

"Albuquerque Bill" wrote in message
...
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


wrote in message
...
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.


  #4  
Old January 24th, 2004, 04:33 PM
Bill
external usenet poster
 
Posts: n/a
Default Lookup with multiple results


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!).
  #5  
Old January 24th, 2004, 07:07 PM
RagDyer
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

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


"Bill" wrote in message
...

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


  #6  
Old January 24th, 2004, 09:36 PM
Bill
external usenet poster
 
Posts: n/a
Default Lookup with multiple results


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


  #7  
Old January 24th, 2004, 10:04 PM
RagDyer
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

"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!
==============================================


"Bill" wrote in message
...

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



  #8  
Old January 24th, 2004, 10:36 PM
external usenet poster
 
Posts: n/a
Default Lookup with multiple results


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
  #9  
Old January 24th, 2004, 10:56 PM
RagDyer
external usenet poster
 
Posts: n/a
Default Lookup with multiple results

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 ?
wrote in message
...

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


  #10  
Old January 25th, 2004, 06:24 PM
Bill
external usenet poster
 
Posts: n/a
Default Lookup with multiple results


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!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.