Returning all matches

Hello,

This has been racking my brain for too long now, and I do hope someone can help me. I have a sheet in which column A is populated with part numbers (internal part numbers). In column E are associated (approved) manufacturer part numbers corresponding to that internal part number. For each internal part number, there is at least one, and up to 8, manufacturer part numbers associated. So, let's say if internal part number XYZ123 is in A2, and has 4 manufacturer part numbers associated with it, XYZ123 will be repeated in cells A2, A3, A4 and A5. Cells E2 to E5 would then each contain a different manufacturer part number.
Here's what I'm trying to do: I would like to find all instances of each internal part number, and return all manufacturer part numbers associated with that internal number. In my example, I would like to search for all instances of XYZ123, and then return ALL of the values from E2:E5 - preferably all concatenated in one cell.
I've attached an example of what I'm getting at - hopefully it helps. Please let me know if there's anything that will solve thi - or if any clarification is needed.


Thanks in advance for your time!

-Keith

Hi,

In addition to Scott's answer, you can take a look at previous post

http://www.excelforum.com/showpost.p...98&postcount=5


HTH
Carim

Originally Posted by Maistrye

If you don't mind Macros/User-Defined Functions (UDF), here's a User-Defined Function that should probably do what you are asking.

You could then just use the function:

=AllMfgNums(A2:A100,E2:E100,"XYZ123") or
=AllMfgNums(A2:A100,E2:E100,A2) .... same as regular functions

If you're not familiar with macros, Debra Dalgleish's site (http://www.contextures.com/tiptech.html) has a couple of pages on Macros (Under M... :-) ), or ask back here.
>snip<

Hi Scott,

You are officially my hero!!!! Your UDF worked perfectly, and taught me a lot about UDFs as a side bonus. I love the flexibility of it, so I can use it to pull up all matches for anything on the sheet with just little tweaking!!!
Not to mention being able to embed this in a workbook, saving the workbook as an Add-In and being able to make it available in any workbook I open - something I never knew before, but learned thanks to your UDF.

Again, I cannot tell you how much I appreciate your help. You have saved me an enormous amount of time (and pain!!).


THANK YOU!!!!

-Keith