formula for auto fill multi data

[mb149]

hello,
must be having a brain fart... can't get a formula to work. I need to take multiple part numbers and pull in all the AVLs that are tied to each one. See example below...

so in cell A2 I would have a part number XYZ, XYZ has 4 approved manufactures. So I would want to pull in all the associated part numbers into cell B2 and have it populate B2 thru B5.
and once there are no more XYZ manufactures A6 will pick up the next part number and auto populate the rows down for as many approved manufactures and so on...

also how do you put a limit on the amount of data you want to see: IE I have 20 AVLs for part numbers but I only want the top first 5 for all of them?


Thanks a bunch for any help!!!

 

[mb149]

see visual example below please...

list part number here		Auto populates thru formula…
Part Number		Part Number	Customer part number	SUPPLIER	Descrtiption	QTY
10001		10001	2356	Target	Blah,BLAH	1
10002		10001	179465	Walmart	Blah,BLAH	2
10005		10001	245	Publix	Blah,BLAH	3
10006		10002	2354	Publix	Blah,BLAH	4
10010		10002	8974	Walmart	Blah,BLAH	5
10012		10002	56178	Target	Blah,BLAH	6
		10002	8946	Chucky cheese	Blah,BLAH	7
		10002	256	petes clothing	Blah,BLAH	1
		10005	47891	Walmart	Blah,BLAH	1
		10006	5247	amazon	Blah,BLAH	2
		10006	8956	target	Blah,BLAH	3
		10010	6541	U S EQUIPMENT CO INC	Blah,BLAH	1
		10010	3256	GLASS & OVERHEAD DOOR INC	Blah,BLAH	1
		10010	564	MILL SUPPLY LLC	Blah,BLAH	1
		10010	7897	MILL SUPPLY LLC	Blah,BLAH	2
		10010	159	HIGHLAND MACHINE WORKS	Blah,BLAH	1
		10010	2841	HIGHLAND MACHINE WORKS	Blah,BLAH	2
		10012	21002	NORTHSIDE SALES CO.	Blah,BLAH	13
		10012	21974	NORTHSIDE SALES CO.	Blah,BLAH	14
		10012	6541	NORTHSIDE SALES CO.	Blah,BLAH	15
		10012	32145	COOK'S PEST PRODUCTS	Blah,BLAH	1

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>