Can this be done through a Pivot Table/Vlookup?

[Tkr567]

Hi all,

Novice here, hope someone can help!

Would appreciate some help on this query and if this can be done with a Pivot table. I have a range of data as per Image 1 below. The end result I'm looking for is a sort of Resource Planner as per Image 2, something that gives me the name of the 'Chargeable' project someone is on if their hours exceed 0 that week.

You'll also see some of my data includes someone on more than one chargeable project in a week, in which case, the first project in the list should appear. Couldn't get my head around how to do it on a Vlookup or other formula so wondering if Pivot Table was an option. I don't have experience in Power Pivot/Query but willing to try anything! Thank you!

 

[sandy666]

use XL2BB to post your example data

 

[veyselemre]

Sub TEST()
    Dim ver(), lst(), krt$
    Dim s1 As Worksheet, s2 As Worksheet
    Dim say&, sat&, i&, ii&
    
    Set s1 = Sheets("Sayfa1")
    Set s2 = Sheets("Sayfa2")

    lst = s1.Range("a2:H" & s1.Cells(Rows.Count, 1).End(3).Row).Value
    ReDim ver(1 To UBound(lst), 1 To 6)

    ver(1, 1) = "Person Name": ver(1, 2) = "Week 1"
    ver(1, 3) = "Week 2": ver(1, 4) = "Week 3"
    ver(1, 5) = "Week 4": ver(1, 6) = "Week 5"
    
    say = 1
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(lst)
            krt = lst(i, 1)
            If Not .exists(krt) Then
                say = say + 1
                ver(say, 1) = krt
                .Item(krt) = say
            End If
            sat = .Item(krt)
            For ii = 2 To 6
                If ver(sat, ii) = "" And lst(i, ii + 2) > 0 Then
                    ver(sat, ii) = lst(i, 3)
                End If
            Next ii
        Next i
    End With
    s2.Cells.ClearContents
    s2.Cells(1, 1).Resize(sat, 6).Value = ver
    
End Sub