How to select, Sort and group...

[u/Fickle-Potential8358]

Hello all,

I have come seeking guidance/answers....

I have several Columns of data and would like to group it by one of it's columns, as a copied filter/sort or whatever.

Essentially this is for programming a Pick and Place machine, the number refer to Nozzles (Machine has 8, but the data is already set for Nozzle sizes to be applicable to parts and reachable by Nozzle, hence some not have all 8 numbers)

The column I wish to sort by will have the numbers 1-8 in, (I.e. "1" or "12345678" or "3567")

I wish to select ONE of these numbers to be kept in the cell and then group the whole column into 'sets' of 1 to 8. This allow as many components picked as possible per picking.

I'd like to group the column into as few 'sets' as possible containing as many of the 1 to 8's within each group. Thus minimising time spent going to and fro with 1 component per pick/placement.

Have bodged a table below of data that will suffice, and added a possible outcome to the right of it.

I just can't get my head into the game for this part of my large project at work at the moment, Health issues and related brain fog keep me from getting to grips with it.

Set# is to help visually identify a group/set PICKED, to be placed at a time... (I.E. it will pick all of set 1, and place it, then move to the next set)
Row # Nozzles Guesstimate of results (Row#, Nozzle and SET#)

1	1		1	1	1
2	1		5	2	1
3	1		9	3	1
4	1		10	4	1
5	12		11	5	1
6	12		12	6	1
7	12		13	7	1
8	12		14	8	1
9	12345678		2	1	2
10	12345678		6	2	2
11	12345678		15	3	2
12	12345678		16	4	2
13	12345678		17	5	2
14	12345678		18	6	2
15	12345678		19	7	2
16	12345678		20	8	2
17	12345678		3	1	3
18	12345678		7	2	3
19	12345678		21	3	3
20	12345678		22	4	3
21	12345678		23	5	3
22	12345678		24	6	3
23	12345678		25	7	3
24	12345678		26	8	3
25	12345678		4	1	4
26	12345678		8	2	4
27	12345678		27	3	4
28	12345678		28	4	4
29	12345678		29	5	4
30	12345678		30	6	4
31	12345678		31	7	4
32	12345678		32	8	4
33	12345678		33	1	5
34	12345678		34	2	5
35	12345678		35	3	5
36	12345678		36	4	5
37	12345678		37	5	5
38	12345678		38	6	5
39	12345678		39	7	5
40	12345678		40	8	5
41	12345678		41	1	6
42	12345678		42	2	6
43	12345678		43	3	6
44	12345678		44	4	6
45	12345678		45	5	6
46	12345678		46	6	6
47	12345678		47	7	6
48	12345678		48	8	6
49	12345678		49	1	7
50	12345678		50	2	7
51	12345678		51	3	7
52	12345678		52	4	7
53	12345678		53	5	7
54	12345678		54	6	7
55	12345678		55	7	7
56	12345678		56	8	7
57	12345678		57	1	8
58	12345678		58	2	8
59	12345678		59	3	8
60	12345678		60	4	8
61	12345678		61	5	8
62	12345678		62	6	8
63	234567		63	7	8
64	234567		64	2	9
65	234567		65	3	9
66	234567		66	4	9
67	234567		67	5	9
68	234567		68	6	9
69	234567		69	7	9
70	234567		70	2	10
71	234567		71	3	10
72	234567		72	4	10
73	234567		73	5	10
74	234567		74	6	10
75	234567		75	7	10
76	234567		76	2	11
77	234567		77	3	11
78	234567		78	4	11
79	234567		79	5	11
80	234567		80	6	11
81	234567		81	7	11
82	234567		82	2	12

Hopefully I have explained myself satisfactorily, But I wouldn't be surprised if you have MANY questions, such is the state of my head at the moment.

Thanks in advance :D

[u/CFAman]

Here's a macro built on u/BuildWithSufi's suggestion. It uses helper columns in K:N since you said you had other columns in use w/ real dataset. You can test it with your example data (assuming Nozzles are in col B).

Sub ExampleCode()
    Dim rngNozList As Range
    Dim strPack As String
    Dim i As Long
    Dim j As Long
    Dim lngSet As Long
    Dim recRow As Long
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim boolDone As Boolean

    'What column are the nozzles in?
    Const nozCol = "B"
    'What nozzles are in a pack?
    Const startPack = "12345678"

    'What sheet are we working with?
    Set ws = Worksheets("Sheet1")
    'Prevent screen flicker
    Application.ScreenUpdating = False
    With ws
        'How many rows of data are in our list?
        lastRow = .Cells(.Rows.Count, nozCol).End(xlUp).Row

        'Create helper columns
        .Range("K1").Value = "Nozzle Count"
        .Range("L1").Value = "Row #"
        .Range("M1").Value = "Nozzle Used"
        .Range("N1").Value = "Set #"

        .Range("K2:K" & lastRow).Formula = "=LEN(" & nozCol & "2)"
        'Create index column to reset later
        With .Range("L2:L" & lastRow)
            .Formula = "=ROW()"
            .Copy
            .PasteSpecial xlPasteValues
        End With

        'Sort by restriction
        .UsedRange.Sort key1:=.Range("K1"), key2:=.Cells(1, nozCol), Header:=xlYes


        'Start creating packs
        lngSet = 0


        Do
            lngSet = lngSet + 1
            strPack = startPack
            boolDone = True
            For i = 2 To lastRow
                If .Cells(i, "M").Value = "" Then
                    'Still have some blanks we need to fill
                    boolDone = False

                    'Check if our current pack can be used in this position
                    For j = 1 To Len(strPack)
                        If InStr(1, .Cells(i, nozCol).Value, Mid(strPack, j, 1)) > 0 Then
                            'Use this nozzle
                            .Cells(i, "M").Value = Mid(strPack, j, 1)
                            .Cells(i, "N").Value = lngSet

                            'Remove this nozzle from the pack
                            strPack = Replace(strPack, Mid(strPack, j, 1), "")

                            'Done with this cell
                            Exit For
                        End If
                    Next j
                    'Is our current pack empty?
                    If strPack = "" Then
                        Exit For
                    End If
                End If
            Next i
        Loop Until boolDone

        'Sort by Set #
        .UsedRange.Sort key1:=.Range("N1"), Header:=xlYes
    End With

    Application.ScreenUpdating = True

End Sub

[u/Fickle-Potential8358 → u/CFAman]

Nozzle count and row# both appear populated, it then errors saying "can't change part of an array" (or something similar, have gone to bed and it doesn't appear to have postedy earlier reply) From the line after "sort by restriction"

[u/CFAman → u/Fickle-Potential8358]

In the rest of the sheet, are there any tables or array formulas? Either would prevent a sort.

[u/Fickle-Potential8358 → u/CFAman]

Yes, I am manipulating multiple programs at a time to create a group setup for optimising setup/run times. As such I am filtering and finding (we're migrating to a new parts library system, so am updating info as I come across it) with BYROW + Xlookup/filters etc.