Hi,
I have a table of two types of product codes (finished products with associated sub-product codes. I want to be able to search the column of sub-codes and identify all the finished product codes associated, and then to use the results of that search to draw data from a separate table.
The tricky part is that some sub-product codes are included within a number of finished product codes, as many as five times in some instances.
This makes using a simple array search inadequate, because it finds only the first instance and then stops searching.
Ive developed a solution that works but I think it might be inelegant and very inefficient. What Ive done is create a set of columns which identify the cell reference of the first instance, then a second one that picks up one row down so picks up the next, and so on. I then use those cell addresses to create the formula that picks the correct value.
Here is an example of the data and formulas I am using where the database is tab is "IE Database" and the production data tab is "Campaign";
SUB Product Data:
<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
Production Data:
<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
IE Data base:
<colgroup><col><col></colgroup><tbody>
</tbody>
This is then the formula that I use to find all the production data;
As you can see, this is insanely complicated but I really see no other way to get this information.
Is there any better and more streamlined way to do this?
Any help is greatly appreciated.
I have a table of two types of product codes (finished products with associated sub-product codes. I want to be able to search the column of sub-codes and identify all the finished product codes associated, and then to use the results of that search to draw data from a separate table.
The tricky part is that some sub-product codes are included within a number of finished product codes, as many as five times in some instances.
This makes using a simple array search inadequate, because it finds only the first instance and then stops searching.
Ive developed a solution that works but I think it might be inelegant and very inefficient. What Ive done is create a set of columns which identify the cell reference of the first instance, then a second one that picks up one row down so picks up the next, and so on. I then use those cell addresses to create the formula that picks the correct value.
Here is an example of the data and formulas I am using where the database is tab is "IE Database" and the production data tab is "Campaign";
SUB Product Data:
Product Code: | |||||
H67135W | 'IE Database'!L5 | 'IE Database'!L6 | 'IE Database'!L7 | 'IE Database'!L12 | 'IE Database'!L16 |
H67128W | 0 | 0 | 0 | 0 | 0 |
H67081W1 | 0 | 0 | 0 | 0 | 0 |
<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
Production Data:
H01321 | 15515 | 515151 | 15156 | 61 | ||
H01322 | 1515 | 155 | 1125 |
<colgroup><col><col span="6"></colgroup><tbody>
</tbody>
IE Data base:
H01321 : | H67135W |
H01322 : | H67135W |
<colgroup><col><col></colgroup><tbody>
</tbody>
This is then the formula that I use to find all the production data;
Code:
=VLOOKUP(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($G22))-1;1;1;1;"IE Database")));Plan_Data;COLUMN(Campaign!J9)-COLUMN(Campaign!$A9);FALSE)*INDEX(UPO_Data;MATCH(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($G22))-1;1;1;1;"IE Database")));Product_Codes_IE;0))+VLOOKUP(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($H22))-1;1;1;1;"IE Database")));Plan_Data;COLUMN(Campaign!J9)-COLUMN(Campaign!$A9);FALSE)*INDEX(UPO_Data;MATCH(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($H22))-1;1;1;1;"IE Database")));Product_Codes_IE;0))+VLOOKUP(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($I22))-1;1;1;1;"IE Database")));Plan_Data;COLUMN(Campaign!J9)-COLUMN(Campaign!$A9);FALSE)*INDEX(UPO_Data;MATCH(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($I22))-1;1;1;1;"IE Database")));Product_Codes_IE;0))+VLOOKUP(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($J22))-1;1;1;1;"IE Database")));Plan_Data;COLUMN(Campaign!J9)+COLUMN(Campaign!$A9);FALSE)*INDEX(UPO_Data;MATCH(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($J22))-1;1;1;1;"IE Database")));Product_Codes_IE;0))+VLOOKUP(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($K22))-1;1;1;1;"IE Database")));Plan_Data;COLUMN(Campaign!J9)-COLUMN(Campaign!$A9);FALSE)*INDEX(UPO_Data;MATCH(CELL("contents";INDIRECT(ADDRESS(ROW(INDIRECT($K22))-1;1;1;1;"IE Database")));Product_Codes_IE;0));0)
As you can see, this is insanely complicated but I really see no other way to get this information.
Is there any better and more streamlined way to do this?
Any help is greatly appreciated.