dividing a list

[moramoga]

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

[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.

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

[moramoga]

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.

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))))