Idea's Needed

[omega0684]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bay ID
[/TD]
[TD]Item Number
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]A1
[/TD]
[TD]111
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]A2
[/TD]
[TD]222
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A3
[/TD]
[TD]222
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A4
[/TD]
[TD]333
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]A5
[/TD]
[TD]222
[/TD]
[TD]100
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Guys,

I have a problem where I have been asked to map out a warehouse in Excel and the operator wants to be able to pull out all the locations of one particular product that is stored in them.

each aisle is mapped out across three columns col1 = Bay ID, col2 = item number col 3 = quantity of item (table above)

each aisle contains between 15-20 bays. so you have a table for each aisle that is 3x15/20 cells.

so what I want is, if I type the item code in a designated cell I get a list of all bays that item code is in. so for example if I typed in "222" I would get bays A2,A3,A5 (from table above)

Make Sense?

Any ideas

All the best

 

[arthurbr]

Have you tried using a filter?

 

[Special-K99]

Or base something on this

with Item Number in D1
in E1
=IFERROR(INDEX($A$2:$A$1000,SMALL(IF(($B$2:$B$1000=D$1),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter
copy down column E for as many rows as there are in column A

NOTE: Array formulas are slow so this may not be practical if you have a very large number of locations

 

[cooper645]

would you not be better off using another column for Aisle number. then store all your data in one table. You could then use the filter for a particular item which avoids the use of formulas and look ups.

You could also the use the data to create pivot tables if you needed to have them all separated on different sheets say.

That would be my solution.