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...
[TABLE="width: 805"]
<tbody>[TR]
[TD]list part number here[/TD]
[TD][/TD]
[TD="colspan: 5"]Auto populates thru formula… [/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD][/TD]
[TD]Part Number[/TD]
[TD]Customer part number[/TD]
[TD]SUPPLIER[/TD]
[TD]Descrtiption[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD="align: right"]10001[/TD]
[TD][/TD]
[TD="align: right"]10001[/TD]
[TD="align: right"]2356[/TD]
[TD]Target[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10002[/TD]
[TD][/TD]
[TD="align: right"]10001[/TD]
[TD="align: right"]179465[/TD]
[TD]Walmart[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10005[/TD]
[TD][/TD]
[TD="align: right"]10001[/TD]
[TD="align: right"]245[/TD]
[TD]Publix[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10006[/TD]
[TD][/TD]
[TD="align: right"]10002[/TD]
[TD="align: right"]2354[/TD]
[TD]Publix[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]10010[/TD]
[TD][/TD]
[TD="align: right"]10002[/TD]
[TD="align: right"]8974[/TD]
[TD]Walmart[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10012[/TD]
[TD][/TD]
[TD="align: right"]10002[/TD]
[TD="align: right"]56178[/TD]
[TD]Target[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10002[/TD]
[TD="align: right"]8946[/TD]
[TD]Chucky cheese[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10002[/TD]
[TD="align: right"]256[/TD]
[TD]petes clothing[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10005[/TD]
[TD="align: right"]47891[/TD]
[TD]Walmart[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10006[/TD]
[TD="align: right"]5247[/TD]
[TD]amazon[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10006[/TD]
[TD="align: right"]8956[/TD]
[TD]target[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]6541[/TD]
[TD]U S EQUIPMENT CO INC[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]3256[/TD]
[TD]GLASS & OVERHEAD DOOR INC[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]564[/TD]
[TD] MILL SUPPLY LLC[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]7897[/TD]
[TD] MILL SUPPLY LLC[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]159[/TD]
[TD]HIGHLAND MACHINE WORKS[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10010[/TD]
[TD="align: right"]2841[/TD]
[TD]HIGHLAND MACHINE WORKS[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10012[/TD]
[TD="align: right"]21002[/TD]
[TD]NORTHSIDE SALES CO.[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10012[/TD]
[TD="align: right"]21974[/TD]
[TD]NORTHSIDE SALES CO.[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10012[/TD]
[TD="align: right"]6541[/TD]
[TD]NORTHSIDE SALES CO.[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10012[/TD]
[TD="align: right"]32145[/TD]
[TD]COOK'S PEST PRODUCTS[/TD]
[TD]Blah,BLAH[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]