Formula Improvement

[BMcHale]

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.


I’ve developed a solution that works but I think it might be inelegant and very inefficient. What I’ve 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:
[TABLE="width: 794"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Product Code:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]H67135W[/TD]
[TD]'IE Database'!L5[/TD]
[TD]'IE Database'!L6[/TD]
[TD]'IE Database'!L7[/TD]
[TD]'IE Database'!L12[/TD]
[TD]'IE Database'!L16[/TD]
[/TR]
[TR]
[TD]H67128W[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]H67081W1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Production Data:
[TABLE="width: 620"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]H01321[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]15515[/TD]
[TD]515151[/TD]
[TD]15156[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]H01322[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1515[/TD]
[TD]155[/TD]
[TD]1125[/TD]
[/TR]
</tbody>[/TABLE]

IE Data base:
[TABLE="width: 161"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]H01321 :[/TD]
[TD]H67135W[/TD]
[/TR]
[TR]
[TD]H01322 :[/TD]
[TD]H67135W
[/TD]
[/TR]
</tbody>[/TABLE]


This is then the formula that I use to find all the production data;

=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.

 

[offthelip]

The obvious way to do that is with VBa. However I can't actually do it for you because I don't understand you requirements: You need to specify where the Product codes are where the sub product codes are , you need to specify how to search the sub codes and how to identify "finished" sub codes . Then having found that, how we are supposed to use that result to "draw information from " a separate table" (where is this table??)
Basically you have missed out more or less everything in your requirements.

 

[BMcHale]

Hi, ok thanks. I'll see if I can explain better.

- The Product codes are in a column and are unique (don't appear on more than one row)
- the sub product codes are in another column but as they may appear inside more than one finished product they will show up on more than one row, connected to multiple finished codes.

These are both in the same tab, the one called "IE Database"

On a separate tab called "Campaign" I have the production information, by week, organised for each finished product code. Again, those are unique and only appear on one row.

So my aim is to work out in each week how much of every sub-product inventory code is used up by the finished products. I have the relationships between finished and subs all established on "IE Database" and I have the volumes for finished codes on "Campaign".

If only there was just a singular sub-to-finished relationship it would be easy, but that's not the case and I cannot affect the sub codes to make that happen, this stuff is a single stock item that can be used on any of it's finished items and we need to keep that flexibility.

Does this help clarify it? If not please let me know and I'll try to clear it up. Thanks for your interest.

 

[offthelip]

Still not clear but getting there. You haven't actually told me where the product code and sub codes are: I am expecting statements like this:
"Product codes are in Column A on the "IE Database " sheet with no header and entered down the sheet with no blanks and each product code is only entered once"
That is Ok,
but the next statement about subproduct codes

the sub product codes are in another column but as they may appear inside more than one finished product they will show up on more than one row, connected to multiple finished codes.

Doesn't really make sense does this mean that each product can only have one sub product, that is what you are implying, or are there multiple columns of sub products?
you also state:

On a separate tab called "Campaign" I have the production information, by week, organised for each finished product code. Again, those are unique and only appear on one row.

You have product information What information you have mentioned product codes and sub codes so far??
How is it organised?? Tell us exactly where it is and how it is laid out, otherwise it is impossible to help. Give us chance!
As an example of the lack of information you are giving us I will tell you how to do it yourself using a similar style: "It is very simple , load all the data into a variant array, then iterate through the product information and output the matching data to the worksheet."
Hopefully you can see that that is just not enough infomation, but that is more or less all I can work out at the moment.

 

[BMcHale]

Sure, I understand. Apologies.

1. The underlying concept is that we might produce a Work in Process item (WIP) which can then be further processed into a number of different final products. This isn't the actual product, but imagine making a generic drink from a process which then goes to a bottling plant to be put into different bottle sizes. The "sub product" would be the generic liquid drink and the final products would be each individual bottled product.

So there would be one column of finished product codes and every code in that column is unique. Alongside it is ONE column to indicate the sub-product that goes into them, but any sub-product code might appear against many final products. There isn't more than one column for sub-product codes. On a third column we have the volume relationship between one unit of finished product and the sub-product (for example, 1 box of finished product contains 1.45 litres of liquid).

2. Regarding the "Campaign" tab, this is quite simple. It consists of one column containing every code (finished or sub-product) grouped into different work centres, and for every week (arranged in 52 columns over a year) we have the planned output againt the unit of measure specific to that item.

My objective is to have a third tab which calculates the expected closing stock of all the sub-product codes for every one of those 52 weeks, calculated by:
- adding the expected production of the sub-product in any week (this is the easy bit!)
- deducting the volume of sub-product expected to be used up during the production of any and all of the finished products in that week.
- then adding the increase or reduction of sub-product to the previous weeks closing stock (another extremely easy bit)


It's this second part that's got me wrapped up in extreme complexity, going hunting to find any finished code that's associated with each sub-product, and then carrying out the calculation.

Here's the data as it appears in the Workbook;

"Campaign"
[TABLE="width: 576"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]Product Codes
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]H01321
[/TD]
[TD]1554
[/TD]
[TD]1344
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]H01322
[/TD]
[TD]
[/TD]
[TD]210
[/TD]
[TD]1554
[/TD]
[TD]1554
[/TD]
[TD]540
[/TD]
[/TR]
[TR]
[TD]H01323
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1014
[/TD]
[/TR]
</tbody>[/TABLE]

"IE Data base"
[TABLE="width: 375"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Code
[/TD]
[TD]WIP
[/TD]
[TD]No.WIP Per Finished Good
[/TD]
[/TR]
[TR]
[TD]H01321
[/TD]
[TD]H67135W
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]H01322
[/TD]
[TD]H67135W
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]H01323
[/TD]
[TD]H67144W
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

So in the example above, sub code H67135W is used in both H01321 and H01322, and in week 2 (column C in “Campaign”) we’d consume 1344 times 4 plus 210 times 4. I want to do this evaluation on the separate tab for every sub-product code, for every week as I indicated above.

Just one point; I'm familiar with using variant arrays but I want to avoid creating superfluous ranges of data.

Hope I've got this clearer, I really appreciate the effort to help and I hope I've got it right this time.

 

[offthelip]

Excellent, you have finally got there with the definition. I can now go ahead and do something, althought there are still some questions such as you haven't told the name of the tab where you want the results or what format. I assume I put the results in "sheet1" and I assume the format will be the same as the campaign tab except the column will have a single entry for each subproduct code that is used.
Also I assume the IE database tab is not sorted on column B

Unfortunately I can't do anything on this at the moment, may be over the weekend.

 

[offthelip]

I found half an hour to do this, this code creates a table very like your campaign table but it is using the sub prodcodes instead of the product codes. It writes the result out to sheet 1
I expect from this you can do all the calculations you need because it gives you the full breakdown by week of the sub codes usage.

Public outarray As Variant
Public Camparray As Variant

Public Lastcamp


Sub product()
Dim product As String
Dim subprod As String
Dim ind As Long
Dim cnt As Long


With Worksheets("IEDatabase")
 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
 ' Load database into variant array with extra column for falgs
 DBarray = Range(.Cells(1, 1), .Cells(lastrow, 4))
 'initialise the "done " flag column
 For i = 1 To lastrow
 DBarray(i, 4) = ""
 Next i
End With
With Worksheets("Campaign")
 Lastcamp = .Cells(Rows.Count, "A").End(xlUp).Row
 ' Load database into variant array
 Camparray = Range(.Cells(1, 1), .Cells(Lastcamp, 53))
 outarray = Range(.Cells(1, 1), .Cells(Lastcamp, 53))
'initialise the outputarray
 For i = 1 To 53
  For j = 2 To Lastcamp
   outarray(j, i) = 0
  Next j
 Next i
 ' initialise the outarray index
indi = 1
' initialise the search with the first sub product code
 subprod = DBarray(1, 2)
' loop through the IE database
 For i = 2 To lastrow
     If DBarray(i, 4) = "" Then
      indi = indi + 1
      subprod = DBarray(i, 2)
       ' output a line to the output array
         product = DBarray(i, 1)
         cnt = DBarray(i, 3)
         ind = indi
         Call writeline(subprod, product, ind, cnt)
         
       DBarray(i, 4) = True
       ' search through the rest of the db
       For j = i To lastrow
         If subprod = DBarray(j, 2) And DBarray(j, 4) = "" Then
           ' output a line to the output array
            product = DBarray(j, 1)
            cnt = DBarray(j, 3)
            Call writeline(subprod, product, ind, cnt)
            DBarray(j, 4) = True
         End If
       Next j
      End If
     Next i
End With
With Worksheets("Sheet1")
  Range(.Cells(1, 1), .Cells(Lastcamp, 53)) = outarray
End With


End Sub


Sub writeline(subprod As String, product As String, ind As Long, cnt As Long)
 ' search for the product code
 For kk = 2 To Lastcamp
  If product = Camparray(kk, 1) Then
   ' found the row so calculate that row
     For jj = 2 To 53
      If Camparray(kk, jj) <> "" Then
      Prodfnd = True
      ' put the sub product code in columnA
       outarray(ind, 1) = subprod
       ' add the quantity of the subproduct
       outarray(ind, jj) = outarray(ind, jj) + cnt * Camparray(kk, jj)
      End If
     Next jj
  End If
 Next kk
End Sub