Top 3 employee gradewise and hours wise

[Sidd29]

Hi, this is the scenario.

I have a list having 10 clients on which different resource charge their hours.

Some resources are trainee, others are Senior.

I want to filter out top 3 Seniors "and" top 3 trainees who have worked on that client during the year. So there maybe 5 seniors or 10 trainees on that code but i want 3 highest seniors and 3 highest trainees as per thw hours charged. And i want this for each of the 10 clients.

Is there any formula or SQL to get the output?
Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us a small set of dummy data and the expected results with XL2BB so that we can better understand what you need?
Two clients instead of 10 should be enough and just include enough other data & results to show how to get the top 2 seniors & trainers for each client.

 

[Sidd29]

Client code	client name	EMP name	Emp Grade	Hours spent
123	ABC	Mark	Senior	20
123	ABC	John	Trainee	40
123	ABC	Sid	Trainee	30
456	XYZ	Ana	Senior	10
456	XYZ	Sidney	Senior	5
456	XYZ	Harsh	Trainee	20
456	XYZ	Matt	Trainee	10

The above table is an example of the data that i have..I want to filter out just one Senior and One Trainee on each client code who have maximum hours. So for example, on client XYZ, Ana and Sidney both are seniors, however since Ana has charged more hours so i want just her name and her hours. Also Harsh and Matt both are trainees, but Harsh has charged more hours so only Harsh's name with his hours..

And this i want for all the client codes...so is there any shortcut with which this data can be extracted?

 

[mohadin]

Hi
See if this what you want

Sub test()
    Dim a As Variant, i, x
    Dim txt
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 5)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            txt = a(i, 1) & Chr(64) & a(i, 2) & Chr(64) & a(i, 4)
            If Not .exists(txt) Then
                .Add txt, Array(a(i, 5), a(i, 3))
            Else
                .Item(txt) = IIf(.Item(txt)(0) > a(i, 5), .Item(txt), Array(a(i, 5), a(i, 3)))
            End If
        Next
        For i = 1 To .Count
            x = Split(.Keys()(i - 1), Chr(64))
            Cells(i, 8).Resize(, UBound(x) + 1) = x
            Cells(i, 8).Offset(, 3).Resize(, 2) = Array(.Items()(i - 1)(0), .Items()(i - 1)(1))
        Next
    End With
End Sub

 

[Peter_SSs]

Thanks for the added information but I now have a few more questions.

1. In your sample, for each client, the employees have one row each. Is it possible that for client XYZ, Ana might have two or more rows that need to be combined or will there only ever be one row per employee per client?
2. What do you want to happen if two or more employees of the same grade work the same equal top number of hours for a particular client?
3. Have you really reduced what you want to just one (top) employee of each grade per client or do you still want the top 3?
4. Can you show how you want the results presented by posting the results you want for the sample data so we can see the desired layout?

 

[mohadin]

Ver.2

Sub test()
    Dim a As Variant, i, x
    Dim txt
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 5)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            txt = a(i, 1) & Chr(64) & a(i, 2) & Chr(64) & a(i, 4)
            If Not .exists(txt) Then
                .Add txt, Array(a(i, 5), a(i, 3))
            Else
                .Item(txt) = IIf(.Item(txt)(0) > a(i, 5), .Item(txt), Array(a(i, 5), a(i, 3)))
            End If
        Next
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Cells(1, 8).Resize(.Count) = Application.Transpose(.Keys)
        With Cells(1, 8).Resize(.Count)
        .TextToColumns , , , , 1, , , , 1
        End With
        Cells(1, 8).Offset(, 3).Resize(.Count, 2) = Application.Transpose(Application.Transpose(.Items))
    End With
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub