I think I get what you mean - it sounds like this would be much better off in a database to me. There it would be pretty simple to come up with a report of all the constituent parts for a given product.
In Excel, I think you would need some sort of recursive routine to check each row for a product, find the component, then look through the list to see what components make up that component, and so on until the lowest level is reached. To do that I think we would need (or at least I would - I'm quite simple!) to see a sample of your data and its layout.
Here is all I got so far. I am still trying to define the concpts I will need so it will work.
Sub LoadFile()
' This sub will load the data in the file.
' The position of the cols in the source file is what determines in which col array it will be loaded.
' It also will create individual arrays or groups of records (rows) to accomodate all sku as a set.
ThisWorkbook.Application.Run "DataSelection"
Dim LoadedSourceData As Variant
Dim LoadedSourceRow As Double
Dim LoadedSourceCol As Double
LoadedSourceRow = 1
' default 1 since inderectly set by Option Base 1 in the iteration
LoadedSourceCol = 1
' default 1 since inderectly set by Option Base 1 in the iteration
LoadedSourceData = Range("A2:N9146").Value
' This creates a 2 dimensional range by default (rows,cols)
Dim MinRow As Double
Dim MaxRow As Double
Dim MinCol As Double
Dim MaxCol As Double
MinRow = LBound(LoadedSourceData, 1) ' default 1 since Option Base 1
MaxRow = UBound(LoadedSourceData, 1)
MinCol = LBound(LoadedSourceData, 2) ' default 1 since Option Base 1
MaxCol = UBound(LoadedSourceData, 2)
ReDim ProductSKU(MaxRow) As String
ReDim ProductDescription(MaxRow) As String
ReDim ProductDescription2(MaxRow) As String
ReDim LotSize(MaxRow) As Double
ReDim ComponentId(MaxRow) As String
ReDim Operation(MaxRow) As String
ReDim CostCenter(MaxRow) As String
ReDim ComponentDescription(MaxRow) As String
ReDim ComponentUOM(MaxRow) As String
ReDim ComponentQTY(MaxRow) As Variant
ReDim ManufacturingTime(MaxRow) As Double
ReDim SetupTime(MaxRow) As Double
ReDim ManLaborId(MaxRow) As String
ReDim DataDate(MaxRow) As Date
ReDim SKUList(1) As String
Dim ProductSKUIndex As Double
Dim SKUListIndex As Double
ProductSKUIndex = 1
SKUListIndex = 0
'Creating arrays for every single column.
For LoadedSourceRow = MinRow To MaxRow
' Using the lower and upper rows limits of the source data
ProductSKU(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow,1)
ProductDescription(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 2)
ProductDescription2(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 3)
LotSize(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 4)
ComponentId(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 5)
Operation(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 6)
CostCenter(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 7)
ComponentDescription(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 8)
ComponentUOM(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 9)
ComponentQTY(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 10)
ManufacturingTime(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 11)
SetupTime(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 12)
ManLaborId(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 13)
DataDate(LoadedSourceRow) = LoadedSourceData(LoadedSourceRow, 14)
Next LoadedSourceRow
For ProductSKUIndex = MinRow To MaxRow
If IsError(Application.VLookup(ProductSKU(ProductSKUIndex), SKUList, 1, False)) = True Then
SKUListIndex = SKUListIndex + 1
ReDim Preserve SKUList(SKUListIndex)
SKUList(SKUListIndex) = ProductSKU(ProductSKUIndex)
Else
End If
Next ProductSKUIndex
MsgBox SKUList(1)
MsgBox SKUList(2)
MsgBox SKUList(3)
MsgBox SKUList(4)
MsgBox SKUListIndex
End Sub
' Done ' Creating arrays for every single column.
' Pending' ' Creating 1 array that contains all sku with no repetitions.
' Pending' Creating One array that contains all row for a single sku for each SKU.
