Results 1 to 7 of 7

Thread: Loop de loop

  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spreadsheet in Excel 2003, that lists employees of a call site, and tracks every time a call is monitored. It's by date, so each employee name appears multiple times. What I want to do is check the first instance of each name, see if that monitoring is "OK", if so go to the next instance of their name and do the same thing. If they have a monitoring that is "NG" then I want to delete all instances of that employee's name from the spreadsheet. The spreadsheet can be refreshed, so that's not a problem. What I want to end up with is literally the last man standing, a listing of the one employee (if there is one) who received an "OK" every time he or she was monitored for a given period of time.

    I've tried several ways, none of which have worked. I've tried doing a FIND ALL in code, which would find all instances of a single employee, but when I deleted the row with the employee's name it broke the loop. Also when that loop was put into a FOR NEXT loop, to get the next employee, it stopped working entirely. I tried using another spreadsheet in the workbook that is used for pulling monitorings of individual employees, but couldn't get the loop to work on the listbox.

    I'm up for any and all suggestions at this point, and any help will be greatly appreciated. Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Here's a solution you may be able to use: The macro in this worksheet will do an advanced filter to pull out a unique list of names. It then creates a work area with Name and NG as headers and will copy the names and place a SumProduct formula that will count the NGs by name. The work area is then copied and replaced with Values {sorting with the formulas active doesn't work!}. The list is then sorted Ascending by NG count then Name Apha so your star performers percoluate to the top.

    The macro depends on some dynamic named ranges {see Define Names}.
    You can paste new data into the Data sheet then switch to the Calculate sheet, enter your date range and run the macro.

    If this doesn't solve your problem maybe it will at least give you some ideas.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    There can be several solutions for what is needed ...

    Here is a Pivot Table to see a count of each employee's NG and OK. It shows a blank for the employee with no "NG".
    After reviewing Pivot Table, in the raw data Sort and Delete any employee that has an "NG"
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tim,

    I tried the Pivot Table first also but couldn't figure out how to get it to only work with a given date range vs the entire data set. Any Ideas how to do this with the Pivot Table?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi RG - There are a few ways to limit output by date range. One of the ways it can be done is with the revised attached. Just double click "Date" and choose dates to hide. There are many more alternatives such as using the Date within the Pivot Table.
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tim,

    Duh {see me whacking myself on the forehead}!

    Thanks for the comeback!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for all of your input. Here's the code I finally used. The "N" = NG and the "Y" = OK., and are in a field called Admin The sort is by name and by Admin, so N comes before Y. When it's finished running, the only row left on the spreadsheet (of 1275 to start) is the winner. Unfortunately, in this case, after 3 days of coding to find the winner, we discovered the winner no longer works there. :-)

    Private Sub btnallperf_Click()

    Dim intRows As Integer
    Dim strName As String
    Dim strFound As String
    Dim strWinner As String
    Dim myrange As Range
    Dim strHigh As Integer
    Dim strHighName As String
    Dim strHighTest As String

    Set oRange = Range(Range("$a$17"), Range("$e$1300").End(xlUp))
    intRows = Application.WorksheetFunction.CountA(Intersect(oRa nge, Range("e:e")))

    Application.ScreenUpdating = False

    'Clear the contents of any previous searches

    Columns("U:U").Select
    Selection.ClearContents
    Range("e17").Select

    'Sort the data on the spreadsheet by name, and then by Admin ("N" comes before "Y")

    Range("A17:T1500").Sort Key1:=Range("E17"), Order1:=xlAscending, Key2:= _
    Range("T17"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal

    'Loop through the names

    For i = 1 To intRows

    If ActiveCell <> "" Then

    If ActiveCell = ActiveCell.Offset(1, 0) Then

    'If there is a single "N" then delete all rows for that employee

    If ActiveCell.Offset(0, 15) = "N" Then
    Do Until ActiveCell <> ActiveCell.Offset(1, 0)
    Rows(ActiveCell.Row).Select
    Selection.Delete shift:=xlUp
    ActiveCell.Offset(0, 4).Select
    Loop

    Rows(ActiveCell.Row).Select
    Selection.Delete shift:=xlUp
    ActiveCell.Offset(0, 4).Select

    'If there is no "N" in Admin then start numbering the rows of "Y"

    Else
    ActiveCell.Offset(0, 16) = 1
    Do Until ActiveCell <> ActiveCell.Offset(1, 0)
    If ActiveCell = ActiveCell.Offset(1, 0) Then
    If ActiveCell.Offset(0, 16) <> "" Then
    ActiveCell.Offset(1, 16) = ActiveCell.Offset(0, 16) + 1
    ActiveCell.Offset(1, 0).Select
    End If
    End If
    Loop

    End If

    If ActiveCell.Offset(0, 16) >= 1 Then
    ActiveCell.Offset(1, 0).Select
    End If

    Else
    If ActiveCell.Offset(0, 15) = "N" Then
    Rows(ActiveCell.Row).Select
    Selection.Delete shift:=xlUp
    ActiveCell.Offset(0, 4).Select

    Else
    ActiveCell.Offset(0, 16) = 1
    Do Until ActiveCell <> ActiveCell.Offset(1, 0)
    If ActiveCell = ActiveCell.Offset(1, 0) Then
    If ActiveCell.Offset(0, 16) <> "" Then
    ActiveCell.Offset(1, 16) = ActiveCell.Offset(0, 16) + 1
    ActiveCell.Offset(1, 0).Select
    End If
    End If
    Loop

    ActiveCell.Offset(1, 0).Select
    End If
    End If

    Else

    'Indicate the winner

    strHigh = Application.WorksheetFunction.Max(Range("u17:u1500 "))

    Range("u17").Select
    Do Until ActiveCell = ""
    If ActiveCell < strHigh Then
    Rows(ActiveCell.Row).Select
    Selection.Delete shift:=xlUp
    ActiveCell.Offset(0, 20).Select
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop

    Application.ScreenUpdating = True

    End

    End If
    Next i

    End Sub

Posting Permissions

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