dividing a list

moramoga

New member
Joined
Sep 3, 2013
Messages
2
Reaction score
0
Points
0
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:

example.png

thanks a lot
 
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
 
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))))
 
Back
Top