Pull data from spreadsheet

[Josefiend]

Hello all, new here bear with me:</SPAN></SPAN>

I have a main inventory spreadsheet with 2 columns: A is for Part_Number, B is for location. This sheet hase 1000s of lines, also, there will be duplicates in each column as there are certain parts in multiple locations, and certain locations with multiple parts.</SPAN></SPAN>

I have a list (on another sheet) with 100 or so distinct part numbers I need to pull from the main spreadsheet. So basically, I need a formula(?), that will look at each part number in the small list and pull EVERY INSTANCE of that part number along with its corresponding location from the main spreadsheet.</SPAN></SPAN>

I have made multiple attempts after much researching online, but I can’t seem to make this happen. It seems like it should be fairly simple.</SPAN></SPAN>

Thank you for any help!</SPAN></SPAN>

 

[AhoyNC]

Maybe something like this.
If you are careful how you set up your cell references you can just drag formula down and across (Cell E2 in the example below).

NOTE: This is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H
1	Part#	Location		Part#	Loc	Loc	Loc	Loc
2	Part1	Loc1		Part1	Loc1	Loc3	Loc7
3	Part2	Loc2		Part2	Loc2	Loc1
4	Part1	Loc3		Part4	Loc4
5	Part4	Loc4		Part6	Loc6
6	Part2	Loc1
7	Part6	Loc6
8	Part1	Loc7
CTRL-SHIFT-ENTER.

 

[Josefiend]

Well this totally worked!! Thank you very much for your help.</SPAN></SPAN>

The only problem now is that the data is hard to look at and sort. I’m wondering if I can get all the data back into two columns. That way, I could do subtotals on either part or location. </SPAN></SPAN>

I’m not sure if all this is the best way to do what I’m trying to do, I’m just thinking subtotals because use them a lot. Or Maybe:</SPAN></SPAN>

It’s time for a pivot table! (shudder)</SPAN></SPAN>

Or a database! (yikes)</SPAN></SPAN>

I’m learning slowly but surely so any input on this is appreciated. Thank you again for your help already!</SPAN></SPAN>