| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data
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 # |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding data
As long as it shows the results horizontally, I don't see the problem
"Don Guillett" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Byron720" wrote in message ... 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 # |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding data
Byron720 wrote:
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 [he 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding data
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. "smartin" wrote: Byron720 wrote: 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 [he 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding data
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=Shee t1!$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=She et1!$A$2:$A$13)*ROW($1:$12)<0,($A2=Sheet1!$A$2:$A $13)*ROW($1:$12)),COLUMN()-1)),"") Byron720 wrote: 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. "smartin" wrote: Byron720 wrote: 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 [he 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding Data | Excel Worksheet Functions | |||
| finding data in same sheet | Excel Discussion (Misc queries) | |||
| Finding data | Excel Discussion (Misc queries) | |||
| Finding max row containing data... | Excel Discussion (Misc queries) | |||
| finding data | Excel Worksheet Functions | |||
Linear Mode
