Finding data

[Byron720]

I hope someone can help me on this:

I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
etc
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically after I typed the part #

 

[Don Guillett]

I would use a macro that makes a unique list and copies it to the other
sheet and then goes down that list using FINDNEXT to add each
find.offset(,1) to the list.

 

[Byron720]

As long as it shows the results horizontally, I don't see the problem

 

[smartin]

I hope someone can help me on this:

I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
etc
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically after I typed the part #

This does not handle lookup errors but if you have E2007 you can
probably figure out how to clean it up.

In Sheet2!B2 enter the following array* formula, then fill right and down:

=INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1))

Caveat: ROW($1:$12) must reference the same number of rows as $A$2:$A$13
and $B$2:$B$13 [here: 12 rows].

Result:

Part# Loc1 Loc2 Loc3
1111 A1 H9 E3
2222 B5 B9 #NUM!
3333 G7 E2 #NUM!
4444 F8 #NUM! #NUM!
5555 A3 #NUM! #NUM!
6666 H2 #NUM! #NUM!
7777 C2 #NUM! #NUM!
8888 D1 #NUM! #NUM!

*Commit the array formula by pressing Ctrl+Shift+Enter; do not just
press Enter or Tab.

 

[Byron720]

Thanks Martin,

It is 99% good. Only one thing. I don't want to see #NUM! everytime the
result is false, so, I guess I can use IF for that. I tried but I just don't
know how to do it.

I hope someone can help me on this:

I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
etc
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically after I typed the part #

This does not handle lookup errors but if you have E2007 you can
probably figure out how to clean it up.

In Sheet2!B2 enter the following array* formula, then fill right and down:

=INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1))

Caveat: ROW($1:$12) must reference the same number of rows as $A$2:$A$13
and $B$2:$B$13 [here: 12 rows].

Result:

Part# Loc1 Loc2 Loc3
1111 A1 H9 E3
2222 B5 B9 #NUM!
3333 G7 E2 #NUM!
4444 F8 #NUM! #NUM!
5555 A3 #NUM! #NUM!
6666 H2 #NUM! #NUM!
7777 C2 #NUM! #NUM!
8888 D1 #NUM! #NUM!

*Commit the array formula by pressing Ctrl+Shift+Enter; do not just
press Enter or Tab.

 

[smartin]

If you have Excel 2003 or earlier the error trap is less than elegant,
but it works:

=IF(ISERROR(INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1))),"",INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1)))

If you have Excel 2007 or later you can probably use this (untested):

=IFERROR(INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1)),"")

Thanks Martin,

It is 99% good. Only one thing. I don't want to see #NUM! everytime the
result is false, so, I guess I can use IF for that. I tried but I just don't
know how to do it.

I hope someone can help me on this:

I have an inventory database where thousands of parts have different bin
locations. What I need is a formula(s) that can tell me all bin locations
where a part is located but horizontally. For example, I have a master data
that looks something like this:

Part # Bin Locations
1111 A1
2222 B5
3333 G7
1111 H9
7777 C2
8888 D1
1111 E3
4444 F8
5555 A3
6666 H2
2222 B9
3333 E2

Then, in sheet 2 my final result should be something like this:

Part # Location 1 Location 2 Location 3 Location 4
etc
1111 A1 H9 E3
2222 B5 B9
3333 G7 E2
4444 F8
5555 A3
6666 H2
7777 C2
8888 D1

Data on Part # column should be entered manually and the Bin Location info
automatically after I typed the part #

This does not handle lookup errors but if you have E2007 you can
probably figure out how to clean it up.

In Sheet2!B2 enter the following array* formula, then fill right and down:

=INDEX(Sheet1!$B$2:$B$13,SMALL(IF(($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)<>0,($A2=Sheet1!$A$2:$A$13)*ROW($1:$12)),COLUMN()-1))

Caveat: ROW($1:$12) must reference the same number of rows as $A$2:$A$13
and $B$2:$B$13 [here: 12 rows].

Result:

Part# Loc1 Loc2 Loc3
1111 A1 H9 E3
2222 B5 B9 #NUM!
3333 G7 E2 #NUM!
4444 F8 #NUM! #NUM!
5555 A3 #NUM! #NUM!
6666 H2 #NUM! #NUM!
7777 C2 #NUM! #NUM!
8888 D1 #NUM! #NUM!

*Commit the array formula by pressing Ctrl+Shift+Enter; do not just
press Enter or Tab.