Determine which products are made of the exact same set of materials

[silverbluemoon]

Hi, all!

I have a requirement to take a bunch of product data and determine exactly which of them use the same materials to make (the info is in a single table). I thought about grouping, but that seems more for calculations. I thought about a pivot table, but I think that is probably overkill. I have watched about 12 YouTube videos at this point, trying to see if I could find someone who could at least give me some pieces of something I could put together to solve this, but I just couldn't get it to come together to meet my needs. So, here I am once again, seeking help from the most experienced Excel users on the planet! I am certain someone here understands what I am trying to accomplish and where my brain block is. Please feel free to tell me that this is the wrong way of going about this and make suggestions. I have a bunch of other things to do with the data once I have sorted it out in some similar way to what I show below, but: a) I understand how to accomplish most of that, and b) I cannot do anything else until I figure this part out. I appreciate your help, you guys. Thanks, in advance!

My Requirements:

Return a list of Product IDs that all have the exact same set of Material IDs. Products that do not share entire sets of materials with any other Product get listed the same as the others, but by themselves. I am imagining something like this (though any variation this would also be okay, such as conditionally formatting the flat table color coding the rows of parts that match, or whatever other options there are.) However it is possible to identify the unique sets of products that are made of the same unique bunch of materials will work for my purposes. I just need to be able to identify the unique groupings.)

Example Spreadsheet layout:

Notes:

• Each Product ID is a unique identifier referring to a finished good composed of several materials.
• Each Material ID is a unique identifier for a specific material.
• Material Descriptions are not unique and may be duplicated as descriptions for different Material IDs.
• All fields are alphanumeric, and IDs may have dashes in them. IDs are not consistent in their structure, not per Product or Material (i.e., some IDs might a structure of XX-F6543, another might look like 54673299A, and another might look like 0000076-GG-HU16, and so on.)

 

[Akuini]

Return a list of Product IDs that all have the exact same set of Material IDs.

Meaning it has nothing to do with Material Description, right?

Could you post your data sample using XL2BB so we can easily copy it to Excel?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Akuini]

Here's an example:
The code will sort data by col A then by col C:

Sub silverbluemoon2()
Dim i As Long
Dim va
Dim d As Object, e As Object

With Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Key2:=.Cells(1, 3), Order2:=xlAscending, Header:=xlYes
    va = .Value
End With

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

    For i = 2 To UBound(va, 1)  'data start at row 2
        
        If Not d.Exists(va(i, 1)) Then
             d(va(i, 1)) = va(i, 3)
        Else
             d(va(i, 1)) = d(va(i, 1)) & "|" & va(i, 3)
        End If
    
    Next

Set e = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
    
    For Each x In d.Keys
        
        If Not e.Exists(d(x)) Then
            e(d(x)) = x
        Else
            e(d(x)) = e(d(x)) & "|" & x
        End If

    Next

'result in col E:F
Columns("E:F").ClearContents
Range("E2").Resize(e.Count, 2) = Application.Transpose(Array(e.Items, e.Keys))

End Sub

Example:
result in col E:F:

Book1
	A	B	C	D	E	F
1	PRODUCT		MATERIAL
2	A01		B01		A01	B01|B02|B03|B04|B05
3	A01		B02		A02|A03	B01|B02|B03|B04|B06
4	A01		B03		A04	B01|B02|B04|B05|B06
5	A01		B04
6	A01		B05
7	A02		B01
8	A02		B02
9	A02		B03
10	A02		B04
11	A02		B06
12	A03		B01
13	A03		B02
14	A03		B03
15	A03		B04
16	A03		B06
17	A04		B01
18	A04		B02
19	A04		B04
20	A04		B05
21	A04		B06
Sheet1

 

[silverbluemoon]

Correct; "Material Description" cannot be trusted in this situation — they are not unique values. Only Product ID and Material ID are unique identifiers. Your answer works! Thank you!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[suraj01]

Here's an example:
The code will sort data by col A then by col C:

Sub silverbluemoon2()
Dim i As Long
Dim va
Dim d As Object, e As Object

With Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Key2:=.Cells(1, 3), Order2:=xlAscending, Header:=xlYes
    va = .Value
End With

Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare

    For i = 2 To UBound(va, 1)  'data start at row 2
       
        If Not d.Exists(va(i, 1)) Then
             d(va(i, 1)) = va(i, 3)
        Else
             d(va(i, 1)) = d(va(i, 1)) & "|" & va(i, 3)
        End If
   
    Next

Set e = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
   
    For Each x In d.Keys
       
        If Not e.Exists(d(x)) Then
            e(d(x)) = x
        Else
            e(d(x)) = e(d(x)) & "|" & x
        End If

    Next

'result in col E:F
Columns("E:F").ClearContents
Range("E2").Resize(e.Count, 2) = Application.Transpose(Array(e.Items, e.Keys))

End Sub

I have very similar requirement. Can anyone explain the excel formula for the above VBA code and how to use it?

 

[suraj01]

Hi all,

As shown in the below image. I have list of salt in column 1 (Column A) and List of unique sku codes in column 2 (Column B). I need the result as shown in the column 4 (Column D)

 

[Akuini]

@suraj01
Try this:

Sub suraj01()
Dim i As Long, j As Long, a As Long
Dim va, vb
Dim spr As String, hh As String, tx As String


With Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
'    .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
    va = .Value
End With

ReDim vb(1 To UBound(va, 1), 1 To 1)
spr = ", " 'separator
For i = 2 To UBound(va, 1)
 j = i
    Do
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1
    
    If i > j Then
        hh = spr & Join(Application.Transpose(Range(Cells(j, "B"), Cells(i, "B")).Value), spr) & spr
    
         For a = j To i
            tx = Replace(hh, spr & va(a, 2) & spr, spr)
            vb(a, 1) = Mid(tx, 3, Len(tx) - 4)
         Next
         
    End If
    
Next

'result in col D
vb(1, 1) = Range("D1")
Columns("D").ClearContents
Columns("D").NumberFormat = "@"
Range("D1").Resize(UBound(vb, 1), 1) = vb

End Sub

Example:

Book1
	A	B	C	D
1	PRODUCT	SKU		RESULT
2	A01	1016		1018, 1013, 1017
3	A01	1018		1016, 1013, 1017
4	A01	1013		1016, 1018, 1017
5	A01	1017		1016, 1018, 1013
6	A02	1005		1007
7	A02	1007		1005
8	A03	1008		1011, 1010
9	A03	1011		1008, 1010
10	A03	1010		1008, 1011
11	A04	1003
12
Sheet1

 

[Sajinjs]

Hi, I recently saw your post and it really helped me. Could you please share the method to add combined data like F column to G column? Example: suppliers to F column