You are welcome.
I have included what I would be ...
Where is it ? 
This is the explanation :
Read data from sheet bom :
Since I thought the memory usage is tight in this case, so I convert the variant array (where each item of its member take 16 bytes, excluding the string itself) to string array (each member only take 4 bytes). I read column by column (column A then column B) to variant array b, which then using looping, writing to string array a. We can save about 900k x 2 columns x (16 - 4 bytes) = 21,600,000 bytes, or about 20 MB of RAM this way.
Next step is, for each unique parent item in column B, we will write down the row number where itself and it's duplicates occurred.
For get the unique parent items, we will use Excel's collection object (z1 in this case), and since the collection cannot change its data directly, we will use array b to store the collection's data.
For example, take item 520/00011/20, this item are listed in these rows : row 1, row 48, row 223 (we exclude the header so row 2 in the cell is row 1 in array, row 49 in in cell is row 48 in array etc). So the value of array b(1) is "1,48,223" where all of item 520/00011/20 is occurred. In other word, array b will be act as a holder of children list.
Now, we need to get all the topmost parent items (a node that has no parent, or the "Top Dog" as shg said).
If the value of cell bom!E2 is not empty, then we only search for this parent item only, otherwise all parent items is calculated.
We add these topmost parent items to second collection (z2). This z2 will act as a "joblist" that should be calculated.
When you said "Recursive reverse BOM or Implosion", you are right, this kind of job must be performed using recursive method, but I will use collection object as a holder of a stack in this case, so the recursive can be avoided.
As we know that at current state, the value of array b is a string (for example above, it is like "1,48,223"). Now we will convert/split this string into individual array member.
We also want the array is sorted, for example if the value are 520/34017/01 and 520/34007/01, we will sort them into 520/34007/01 520/34017/01
We write back the array (from the splitted string) into array b (so array b now is converted from a string array to a jagged array of Long):
As the code above, we put the unique parent items of column B into collection z2 by these lines :
but these items are not really the most "Top Dog" items. For example on cell bom!B3077 (520/34007/04), if we look cell bom!A3108, this "parent" has another parent (003/21064/00), so this is not an actual "Top Dog", we must delete this from collection z2, by using this code :
Now we already have the "Top Dogs", but these "Top Dogs" are not sorted yet (remember, you have another sort routine above, but that is for the "children", not for the Top Dogs).
Since you have big data (900k), we will use the fastest sort routine : QuickSort. In the next lines, we prepare the Quicksort by writing collection z2 to array v1, then sort it, then read back to collection z2.
At this point, all the preparations is completed, and we ready the actual writing to the cell.
Some preparations (delete old output sheets if they are exists, create a new one, etc :
Now we do the "recursive", the job is listed on collection z2, we will proceed one by one the items of z2, everytime an item has been processed, we delete this item from the collection z2, so if there is no more item remained on z2 (z2.count = 0), it means all the job has been calculated and we can exit the Do..Loop.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks