CODEWITHSUNDEEP
excelvba
RawrRawr7
RawrRawr7

Reputation: 353

Is there a way to group any number of cells into a table?

I have a column of cells where I can have any given number of items. The number of items can vary and the quantity can also vary. (See pic) I was wondering would there be a way to group these items into a table or range?

enter image description here

So in this example, I have 5 cookies, 3 Croissants, 3 Oreos and 4 Fruit Cakes. I am wanting to make these items in a range so that group 1 contains all the cookies, group 2 contains all the croissants, group 3 contains all the oreos, and group 4 contains all the Fruit Cakes. Now each time the number of Cookies, croissants, oreos and Fruit Cakes changes, as well we can be adding new items to the list, i.e. Cheese Cake, Chocolate Cake, etc.. Can this be done?

Edit:

enter image description here

Im trying to group them so that they look something like this.

Upvotes: 1

Views: 46

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, test the next code. It assumes that the values to be processed are in column "A:A", starting from "A2". The processing result is dropped in "F1". It can be dropped where you need:

Sub testSplitColPerCageg()
 Dim sh As Worksheet, arr, arrFin, El, dict As Object 
 Dim i As Long, j As Long, k As Long, maxRows As Long, maxK As Long
 
 Set sh = ActiveSheet
 arr = sh.Range("A2:A" & sh.Range("A" & rows.count).End(xlUp).row).Value
 Set dict = CreateObject("Scripting.Dictionary")
 For Each El In arr
    If Not dict.Exists(El) Then
        dict.Add El, 1
    Else
        dict(El) = dict(El) + 1
    End If
 Next
 maxRows = WorksheetFunction.Max(dict.Items)
 ReDim arrFin(1 To maxRows, 1 To dict.count): k = 1
 For Each El In dict.Keys
    i = i + 1
    For j = 1 To CLng(dict(El))
        arrFin(k, i) = El: k = k + 1
    Next
    k = 1
 Next
 sh.Range("F1").Resize(UBound(arrFin), UBound(arrFin, 2)).Value = arrFin
End Sub

Upvotes: 1

Related Questions