Results 1 to 3 of 3

Thread: dividing a list

  1. #1

    dividing a list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    Click image for larger version. 

Name:	example.png 
Views:	9 
Size:	5.8 KB 
ID:	1637

    thanks a lot

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  3. #3
    Quote Originally Posted by NoS View Post
    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))))

Posting Permissions

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