# Create Random Unique Groups of 5, Keep Duplicates in Column

#### erinmc42

##### New Member
I have a spreadsheet of thousands of books that need to be broken down into groups of 5 titles. I want to know how many unique groups can be created from this list. The catch is, some titles have 600 copies, while some have only 1.

How can Excel create my groups of books?

Also, do I have any other choice but to begin with a column with 635 of ON THE FARM in every row of the column, followed by 443 of MARS, etc?

Thank you for any assistance.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Sulprobil

##### Board Regular
You can randomly create groups of 5 until you have less than 4 different books left.
The random selection can use your book count as weight, you can "draw" On the farm with weight 635 and you will "draw" I can run only with weight 192, for example.
I created a simple simulation:
MrExcel_Create Random Unique Groups of 5 Keep Duplicates in Column.xlsm
ABCDEFGH
1NumberCountTitleI want groups of5CheckRest
21635On the farm57659
32443Mars4412
43329Let's explore the stars3290
54300Cat got a lot3000
65266Being Present2660
76253Garden Day!2530
87252Jonathan cleaned up … then he heard a sound2520
98226Aaron is a good sport2260
109224Rocket the brave!2240
1110222Story of Ferdinand2220
1211220Soccer Time!2200
1312209Becoming a Salamander2090
1413192I can run1920
Input
Cell Formulas
RangeFormula
G2:G14G2=COUNTIF(Output!\$1:\$1048576,Input!A2)
H2:H14H2=B2-G2

VBA Code:
``````Option Explicit

Enum book_info_columns
bicNumber = 1
bicCount
bicTitle
End Enum

Sub CreateGroups()
Dim i As Long, j As Long, lBooks As Long, lGroups As Long, lSize As Long, lTotal As Long
Dim vI As Variant, vT As Variant
Dim state As SystemState    'See https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/classes/systemstate

With Application.WorksheetFunction
Set state = New SystemState 'Speed up VBA

'First part - Read in book information and group size
vI = Range(wsI.Cells(2, bicNumber), wsI.Cells(2, bicTitle).End(xlDown))
lSize = Range("GroupSize")

'Second part - Create groups
Randomize
lBooks = UBound(vI, 1)
lTotal = .Sum(.Index(.Transpose(vI), bicCount))
ReDim vO(1 To lSize, 1 To lTotal \ lSize) As Variant
Do While CountNonZero(vI) >= lSize
lGroups = lGroups + 1
vT = .Index(.Transpose(vI), bicCount)
For i = 1 To lSize
j = Int(sbRandHistogrm(1#, CDbl(lBooks) + 1#, vT)) 'See https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/sbrandhistogrm
vT(j) = 0
vO(i, lGroups) = j
vI(j, bicCount) = vI(j, bicCount) - 1
Next i
Loop
ReDim Preserve vO(1 To lSize, 1 To lGroups) As Variant

'Third part - Fill output sheet
wsO.Cells.ClearContents
Range(wsO.Cells(1, 1), wsO.Cells(lGroups, lSize)).FormulaArray = .Transpose(vO)
wsI.Calculate
Set state = Nothing 'Not even necessary - will be done automatically
End With
End Sub

Function CountNonZero(v As Variant) As Long
Dim i As Long, n As Long
For i = LBound(v, 1) To UBound(v, 1)
If v(i, bicCount) <> 0 Then n = n + 1
Next i
CountNonZero = n
End Function``````

There are better ways to use up almost all books but this approach was fairly easy - and it's "random".

Replies
0
Views
31
Replies
11
Views
302
Replies
1
Views
295
Replies
1
Views
199
Replies
4
Views
182

1,127,706
Messages
5,626,396
Members
416,180
Latest member
Excel_user12321

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back