Referencing data without duplicate values

Question

I have the following table:

Our main category is fruit/vegetable, its subcategory is their type and the subcategory of their type is the color. For hours I am trying to achieve to following output with a formula, but without success..

The main product type should not duplicate itself in the row. If there are duplicates within the "fruit/vegetable type" we need to take the type only once and all of its colors. Sorry if the explanation is not good. Here is it graphically:

Answer 1

This is a solution using a dictionary.

Sub test()
    Dim vDB, vR()
    Dim Dic As Object 'Dictionary
    Dim Fruit As Object 'Dictionary
    Dim Ws As Worksheet, toWs As Worksheet
    Dim i As Long, j As Long, r As Long
    Dim k As Integer
    
    Set Ws = Sheets(1) 'set your data Sheet
    Set toWs = Sheets(2) 'set your result Sheet
    
    vDB = Ws.Range("a1").CurrentRegion
    
    Set Dic = CreateObject("Scripting.Dictionary")
    Set Fruit = CreateObject("Scripting.Dictionary")
    
    For i = 2 To UBound(vDB, 1)
        If Dic.Exists(vDB(i, 1)) Then
        Else
            Dic.Add vDB(i, 1), vDB(i, 1)
        End If
    Next i
    
    r = Dic.Count
    
    ReDim vR(1 To r, 1 To 1000)

    For i = 1 To r
        vR(i, 1) = Dic.Items(i - 1)
        k = 1
        For j = 2 To UBound(vDB, 1)
            If vDB(j, 1) = Dic.Items(i - 1) Then
                If Fruit.Exists(vDB(j, 2)) Then
                    k = k + 1
                    vR(i, k) = vDB(j, 3)
                Else
                    Fruit.Add vDB(j, 2), vDB(j, 2)
                    k = k + 2
                    vR(i, k - 1) = vDB(j, 2)
                    vR(i, k) = vDB(j, 3)
                End If
            End If
        Next j
    Next i
        
    With toWs
        .Range("a1").CurrentRegion.Clear
        .Range("a1").Resize(r, 1000) = vR
    End With
    
End Sub