r/excel • u/Fickle-Potential8358 1 • 1d ago

solved How to select, Sort and group...

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

3 Upvotes

67% Upvoted

21 comments sorted by

4

u/GregHullender 195 1d ago edited 1d ago

This is a Makespan Minimization problem. It is NP-complete. This is a job for a professional package--not Excel.

Correction: This specific form--where each task takes the same amount of time--actually does have a polynomial solution.

Scheduling jobs with equal processing times subject to machine eligibility constraints | Journal of Scheduling | Springer Nature Link

I couldn't find a free copy of a paper with the algorithm, but I'll bet the authors would give you one if you e-mailed and asked for it.

1

u/Fickle-Potential8358 1 1d ago

Doesn't sound like you quite understood the assignment to me.

1

u/GregHullender 195 14h ago

It's a well-known problem. Or are you just asking for any assignment that works--not the one with the fewest steps?

1

u/Penguinase 7 1d ago

i'm probably misunderstanding, but are you looking for output like (D,E,F) below?

+ A B C D E F
1 Row # Nozzles   Row# Nozzle Set#
2 1 1   1 1 1
3 2 1   2 1 2
4 3 1   3 1 3
5 4 1   4 1 4
6 5 12   5 2 1
7 6 12   6 2 2
8 7 12   7 2 3
9 8 12   8 2 4
10 9 12345678   9 8 1
11 10 12345678   10 8 2
12 11 12345678   11 8 3
13 12 12345678   12 8 4
14 13 12345678   13 1 5
15 14 12345678   14 2 5
16 15 12345678   15 3 5
17 16 12345678   16 4 5
18 17 12345678   17 5 5
19 18 12345678   18 6 5
20 19 12345678   19 7 5
21 20 12345678   20 8 5
22 21 12345678   21 1 6
23 22 12345678   22 2 6
24 23 12345678   23 3 6
25 24 12345678   24 4 6
26 25 12345678   25 5 6
27 26 12345678   26 6 6
28 27 12345678   27 7 6
29 28 12345678   28 8 6
30 29 12345678   29 1 7
31 30 12345678   30 2 7
32 31 12345678   31 3 7
33 32 12345678   32 4 7

1

u/Fickle-Potential8358 1 23h ago

Close, in my example I gave row# and nozzles available for use and then an empty column then a finished example (done by hand ) of row#, nozzle and set it could be in.

Would have expected set#1 to have all 8 Nozzles used, leaving any unfilled pick runs of the nozzle until last.... You haven't shown all the data, so you may have it right....

1

u/Penguinase 7 13h ago

sorry this was the whole example output from your sample data:

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

2

u/Fickle-Potential8358 1 11h ago

Yes, Thats' ideally what I was looking for. (What witchcraft did you use?)

2

u/Penguinase 7 11h ago edited 11h ago

if you're using excel then the below VBA should work given the sample data structure (Alt+F11 -> Insert -> Module). Then Alt+F8 to run it. It's a greedy most-constrained-first approach so might not be mathematically the minimum for every case, but should be pretty optimal in practice.

ETA: if you only have google sheets i could probably port it to javascript or w/e that uses.

Option Explicit

Sub GroupNozzlesIntoSets()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim n As Long
    n = lastRow - 1 ' assumes row 1 = headers, data starts row 2

    Dim rowNum() As Long, nozzleStr() As String
    Dim assigned() As Boolean, assignedNozzle() As Long, assignedSet() As Long
    Dim allowedArr() As Boolean, cardinality() As Long
    ReDim rowNum(1 To n): ReDim nozzleStr(1 To n)
    ReDim assigned(1 To n): ReDim assignedNozzle(1 To n): ReDim assignedSet(1 To n)
    ReDim allowedArr(1 To n, 1 To 8): ReDim cardinality(1 To n)

    Dim i As Long, j As Long
    For i = 1 To n
        rowNum(i) = ws.Cells(i + 1, "A").Value
        nozzleStr(i) = CStr(ws.Cells(i + 1, "B").Value)
        cardinality(i) = 0
        For j = 1 To 8
            If InStr(nozzleStr(i), CStr(j)) > 0 Then
                allowedArr(i, j) = True
                cardinality(i) = cardinality(i) + 1
            End If
        Next j
    Next i

    Dim remaining As Long: remaining = n
    Dim setNum As Long: setNum = 0
    Dim usedNozzle(1 To 8) As Boolean
    Dim k As Long, madeProgress As Boolean
    Dim bestI As Long, bestCard As Long, bestNozzle As Long
    Dim availCount As Long, firstAvail As Long

    Do While remaining > 0
        setNum = setNum + 1
        For k = 1 To 8: usedNozzle(k) = False: Next k
        madeProgress = True

        Do While madeProgress
            madeProgress = False
            bestI = 0: bestCard = 999

            For i = 1 To n
                If Not assigned(i) Then
                    availCount = 0: firstAvail = 0
                    For j = 1 To 8
                        If allowedArr(i, j) And Not usedNozzle(j) Then
                            availCount = availCount + 1
                            If firstAvail = 0 Then firstAvail = j
                        End If
                    Next j
                    If availCount > 0 And cardinality(i) < bestCard Then
                        bestCard = cardinality(i)
                        bestI = i
                        bestNozzle = firstAvail
                    End If
                End If
            Next i

            If bestI > 0 Then
                assigned(bestI) = True
                assignedNozzle(bestI) = bestNozzle
                assignedSet(bestI) = setNum
                usedNozzle(bestNozzle) = True
                remaining = remaining - 1
                madeProgress = True
            End If
        Loop
    Loop

    ws.Cells(1, "D").Value = "Row#"
    ws.Cells(1, "E").Value = "Nozzle"
    ws.Cells(1, "F").Value = "Set#"
    For i = 1 To n
        ws.Cells(i + 1, "D").Value = rowNum(i)
        ws.Cells(i + 1, "E").Value = assignedNozzle(i)
        ws.Cells(i + 1, "F").Value = assignedSet(i)
    Next i

    MsgBox "Done. " & setNum & " sets created.", vbInformation
End Sub

1

u/Fickle-Potential8358 1 10h ago

Awesome. Working a treat.

1

u/[deleted] 10h ago

[deleted]

1

u/AutoModerator 10h ago

Saying Solved! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Fickle-Potential8358 1 7h ago

Solution Verified.

1

u/Decronym 10h ago edited 7h ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
BYROW Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LEN Returns the number of characters in a text string
ROW Returns the row number of a reference

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 17 acronyms.
[Thread #49285 for this sub, first seen 1st Sep 2026, 19:22] [FAQ] [Full list] [Contact] [Source code]

0

u/[deleted] 1d ago

[removed] — view removed comment

1

u/Fickle-Potential8358 1 1d ago

That sounds right to me.....
Bear in mind I have other columns (Both sides) which have associated (X,Y,Designators, etc...) values I'll need to tweak the code to include.

0

u/CFAman 4828 1d ago

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

1

u/Fickle-Potential8358 1 1d ago

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"

1

u/CFAman 4828 11h ago

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

1

u/Fickle-Potential8358 1 10h ago

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.