1. ## dividing a list

Hello,

I have a simple task to do at work almost daily and I think it can be done easily with some help. There is a table with a single column "PC name". I have to divide the list of PC's into waves.

Wave 1 : 2%
wave 2: 3%
wave 3: 25%
wave 4: 45%
wave 5: 25%

So what I usually do is to copy the list of PC's in excel and add a column named "wave assign". So for example if the list is 100pc's first 2 PC's will be assign to wave 1, 3 PCs to wave 2, 25 PCs to wave 3 and so on. I need a way to automate this since it takes me too long to do it manually. It doesn't matter if there is a small change in the % in order to round up the number of PCs in each wave.

For example: thanks a lot  Reply With Quote

2. This works for me putting it into the sheet's module.
Waves 1,2, 3 and 5 will round up and Wave 4, the largest group, will be reduced so total number of computers will be correct.

Code:
```Sub AssignWaveGroup()
Dim TotalComputers As Double
Dim Wave(0 To 4) As Double
Dim startWave As Long
Dim endWave As Long
Dim i As Integer

TotalComputers = Cells(Rows.Count, "A").End(xlUp).Row - 1

With Application.WorksheetFunction
Wave(0) = .RoundUp(TotalComputers * 0.02, 0)
Wave(1) = .RoundUp(TotalComputers * 0.03, 0)
Wave(2) = .RoundUp(TotalComputers * 0.25, 0)
Wave(4) = .RoundUp(TotalComputers * 0.25, 0)
Wave(3) = TotalComputers - Wave(0) - Wave(1) - Wave(2) - Wave(4)
End With

For i = 0 To 4
startWave = Cells(Rows.Count, "B").End(xlUp).Row + 1
endWave = startWave - 1 + Wave(i)
Range("B" & startWave & ":B" & endWave).Value = i + 1
Next i

End Sub```  Reply With Quote

3. Originally Posted by NoS This works for me putting it into the sheet's module.
Waves 1,2, 3 and 5 will round up and Wave 4, the largest group, will be reduced so total number of computers will be correct.

Code:
```Sub AssignWaveGroup()
Dim TotalComputers As Double
Dim Wave(0 To 4) As Double
Dim startWave As Long
Dim endWave As Long
Dim i As Integer

TotalComputers = Cells(Rows.Count, "A").End(xlUp).Row - 1

With Application.WorksheetFunction
Wave(0) = .RoundUp(TotalComputers * 0.02, 0)
Wave(1) = .RoundUp(TotalComputers * 0.03, 0)
Wave(2) = .RoundUp(TotalComputers * 0.25, 0)
Wave(4) = .RoundUp(TotalComputers * 0.25, 0)
Wave(3) = TotalComputers - Wave(0) - Wave(1) - Wave(2) - Wave(4)
End With

For i = 0 To 4
startWave = Cells(Rows.Count, "B").End(xlUp).Row + 1
endWave = startWave - 1 + Wave(i)
Range("B" & startWave & ":B" & endWave).Value = i + 1
Next i

End Sub```
Thanks a lot! I will try it ASAP. Also this worked if you want to try it, thanks!

=IF(COUNTA(A\$1:A1)<2*COUNTA(A:A)/100,1,IF(COUNTA(A\$1:A1)<5*COUNTA(A:A)/100,2,IF(* ​ COUNTA(A\$1:A1)<30*COUNTA(A:A)/100,3,IF(COUNTA(A\$1:A1)<75*COUNTA(A:A)/100,4,5))))  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•