herpasymplex10
Board Regular
- Joined
- May 26, 2005
- Messages
- 224
Ok. I will provide examples below.
Couldn't do so previously:
Excel 2007
The formula I'm looking to create would go start in H.
This formula wants to look at the Item at column B's (corresponding Row) value
Then it is going to search through the below (seperate worksheet)
for that value and return the component item that is next to it.
The trick is that a number of the Items in Column B have more than one Component in the below.
Not sure if this helps, but the number of components an Item has is listed in Column M above.
It also gets tricky because as you can see in the above example Item 25051 (which as shown in Column M) has 2 components but the part number is listed 4 times.
The formula would need to recognize (if possible) how many components each Item has, search and return those two components only then restart for the next Item number (even if the next Item number is the same!)
Is this possible or should I just convince the powers that be that the format of the spreadsheet needs to be revised completely to allow a function like this to work?
Excel 2007
Couldn't do so previously:
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | H | M | |||
1 | Resource | Item | Component | # of Comp | ||
59 | COUT1040 | 25051 | 2 | |||
60 | COUT1040 | 25051 | 2 | |||
61 | COUT1040 | 25051 | 2 | |||
62 | COUT1040 | 25051 | 2 | |||
82 | ENDROP1179 | 2501019 | 2 | |||
83 | ENDROP1179 | 2501019 | 2 | |||
84 | ENDROP1179 | 2501019 | 2 | |||
Snag |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M59 | =VLOOKUP(B59,Data!A:H,8,FALSE) | |
M60 | =VLOOKUP(B60,Data!A:H,8,FALSE) | |
M61 | =VLOOKUP(B61,Data!A:H,8,FALSE) | |
M62 | =VLOOKUP(B62,Data!A:H,8,FALSE) | |
M82 | =VLOOKUP(B82,Data!A:H,8,FALSE) | |
M83 | =VLOOKUP(B83,Data!A:H,8,FALSE) | |
M84 | =VLOOKUP(B84,Data!A:H,8,FALSE) |
The formula I'm looking to create would go start in H.
This formula wants to look at the Item at column B's (corresponding Row) value
Then it is going to search through the below (seperate worksheet)
for that value and return the component item that is next to it.
The trick is that a number of the Items in Column B have more than one Component in the below.
Not sure if this helps, but the number of components an Item has is listed in Column M above.
It also gets tricky because as you can see in the above example Item 25051 (which as shown in Column M) has 2 components but the part number is listed 4 times.
The formula would need to recognize (if possible) how many components each Item has, search and return those two components only then restart for the next Item number (even if the next Item number is the same!)
Is this possible or should I just convince the powers that be that the format of the spreadsheet needs to be revised completely to allow a function like this to work?
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | Bill Item Name | Component Item Name | ||
2 | 122ST | 122 | ||
3 | 122ST | RGW32 | ||
4 | 124 | 122 | ||
5 | 124 | RGX265 | ||
6 | 125 | 122 | ||
7 | 1400700120 | 601200987500002 | ||
8 | 15002 | 601500956250002 | ||
9 | 15008 | 601501062500002 | ||
10 | 15008 | 85129 | ||
11 | 15010 | 601200987500002 | ||
12 | 15010 | 2901000024 | ||
Data |