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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|