Complex? sorting

Hi guys,

I'm trying to sort my data in Excel 2010. I need to convert a set of data in rows (sheet 1) to 7 different groups on sheet 2. The sorting is based om string data in colom K (represented here by numbers for ease).
Group1 gets all rows with value 1 in colom K; Group2 gets all rows with value 2 in colom K; etc

Sheet 1
A (B-J) K
1 text1 (bunch of other cells) 1,4
2 text2 (bunch of other cells) 2,3
3 text3 (bunch of other cells) 1,2
4 text4 (bunch of other cells) 1,7
5 text5 (bunch of other cells) 0

Sheet 2
A B C D etc
Group1 Group2
1 text1 1,4 text2 2,3
2 text3 1,2 text3 1,2
3 text4 1,7

My question for you: what is the best method to do this sorting? I did some searching on the web, but most methods I found are for more 'simpeler 'sorting actions. Should I use pivot tables, VBA or 'cell formulas'?
Who can point me in the right direction?

Thanks!

Schzuki

P.S. As you might have guessed I'm kind of new to this, all help is welcome!

There goes the layout, see attachement for the proper layout

You're quick Sixtsense!
Was working on that already

Okay, here goes

The data (in sheet 'Data') has to be sorted in sheet ('output')
The input data is presented in rows (each row contains 1 data entry)

Each row has to be sorted according to all categories (cat1; cat2 etc.) mentioned in colom H ('Categories').

E.G:
Substance /~/ Categories
Substance2 /~/ cat1,cat2 -->goes in both cat1 and cat 2

Finally, you schould end up with a sheet that shows each substance and its categories in its appropriate colom.
I've added a file that shows the input and the result. Hope it helps!

Try the attached

Hi Schzuki,
try it

Sub ertert()
Dim x, y(), i&, j&, k&, n&, s$, sp, r()
With Sheets("data")
    x = .Range("A1:H" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
ReDim y(1 To UBound(x), 1 To 2): ReDim r(1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(x)
        sp = Split(x(i, 8), ",")
        For j = 0 To UBound(sp)
            s = Trim(sp(j))
            If .Exists(s) Then
                n = .Item(s): r(n) = r(n) + 1
                y(r(n), n - 1) = x(i, 1): y(r(n), n) = x(i, 8)
            Else
                k = k + 2: .Item(s) = k
                If k > UBound(y, 2) Then ReDim Preserve y(1 To UBound(x), 1 To k): ReDim Preserve r(1 To k)
                y(1, k - 1) = s: y(2, k - 1) = x(i, 1): y(2, k) = x(i, 8): r(k) = 2
            End If
        Next j
    Next i
End With
With Sheets("output")
    .UsedRange.ClearContents
    .Range("A1").Resize(WorksheetFunction.Max(r), k).Value = y()
    .Activate
End With
End Sub

Thanks guys! As far as I can tell so far, this seems to do the trick. Couldn't have done this myself!

@Nilem: If it isn't too much trouble, could you please explain which part of your code performs what action? (I do get the general idea, but I would like to understand it exactly)