OK I see your file and your problem is pretty complicated but I see a possible solution. I will give you enough code to get you started and if my code makes sense you can add to it and finish it off.
What I have done is a subroutine that will list all the tasks in column A, the Subtask in column B, the SubSub in C and the Description in C in a grouped manner. I only tested it once but if you think it does the trick then you have to figure out the formatting to go in the Form sheet.
To use this code first open your Excel file that you attached to the topic, add a new sheet and call it
test.
Then copy the below code to you module and run it. Once finished check the output on the test sheet. Let me know how it goes??
Code:
Sub a()
Dim ar()
ar = Array("Scope", "Responsibility", "Scope of authority", "Additional info", "KPI", "Key competency", "Connection", "Knowledge", "Task")
short_position = Sheets("Form").Range("E4").Value
subT = ""
subSub = ""
For i = 0 To 8
r = r + 1
Sheets("test").Cells(r, 1) = ar(i)
For e = 2 To 2000
If InStr(1, Sheets("Requirements").Cells(e, 2), short_position) > 0 And Sheets("Requirements").Cells(e, 3) = ar(i) Then
If subT <> Sheets("Requirements").Cells(e, 4) Then
r = r + 1
Sheets("test").Cells(r, 2) = Sheets("Requirements").Cells(e, 4)
subT = Sheets("Requirements").Cells(e, 4)
End If
If subSub <> Sheets("Requirements").Cells(e, 5) Then
r = r + 1
Sheets("test").Cells(r, 3) = Sheets("Requirements").Cells(e, 5)
subSub = Sheets("Requirements").Cells(e, 5)
End If
r = r + 1
Sheets("test").Cells(r, 4) = Sheets("Requirements").Cells(e, 1)
End If
Next e
Next i
Topic Author
