Summarise Data

[gplhl]

Hi All, I am having a brain block on how to summarize some data. I have a table with employee names in one row, and skill set status in a sheet (Its not a table, creating a table will cause problems for end users), with the skill set names in a top row. I am wanting a print out that shows by:

Status
Skill Set
Name

E.G.

Required
Iosh Working Safely
John
Paul
Ringo

Real names are in the left of job title, cant post these.

Unable to upload mini sheet due to IT restrictions.

Thanks

 

[gplhl]

Thought I would post an update, I have managed to get the result I wanted by writing a macro with iterations, "for i to", to repeatedly convert data to a list in another sheet. I was really hoping to avoid as our network regularly messes up workbooks with VBA modules in them. Once its in a list, its easy to then create a pivot table from that and users can then use slicers to filter. If anyone else has an idea how I can achieve this list without the use of a macro, I would be really interested.

 

[BigDawg15]

Can you please show the macro so anyone looking at this thread can see one solution to the question.

Thanks,

BigDawg15

 

[gplhl]

Can you please show the macro so anyone looking at this thread can see one solution to the question.

Thanks,

BigDawg15

Code uses two helper formulas that determine How many Skills there are and how many employees, turns off screen updating until the end, to speed up and displays status to user, in status bar. Our network sometimes runs slow, this gives feedback that its working. Just need to add in an error trap. Probably more efficient ways of doing this, it works. Happy to hear other ways.

Sub ChangeToList()

NumberOfSkills = Sheets("Matrix (Status)").Range("A12").Value
NoOfEmployees = Sheets("Matrix (Status)").Range("A13").Value
Status = 0

Application.ScreenUpdating = False

'Get to starting Points, clear previous Summary Data
Sheets("Summary_Data").Activate
Sheets("Summary_Data").Range("A2:C10000").Select

'Selection.ClearContents
Sheets("Summary_Data").Range("A2").Select
Sheets("Matrix (Status)").Activate
Sheets("Matrix (Status)").Range("C15").Select
'End of getting to Starting Points

SkillName = ActiveCell.Offset(-1, 4).Value
SkillColumn = 4 ' initial first colum with Skill

For i = 1 To NumberOfSkills ' loop to bring skills over, number of times to iterate taken from Cell A12, that determines how many columns are populated

Sheets("Matrix (Status)").Activate ' just making sure I am on the right sheet!
Sheets("Matrix (Status)").Range("C15").Select

SkillName = ActiveCell.Offset(-1, SkillColumn).Value

    For j = 1 To NoOfEmployees 'loop to bring Employees over, number of times to iterate taken from Cell A13, that determines how many Rows are populated
    TeamMember = ActiveCell.Value
    SkillStatus = ActiveCell.Offset(0, SkillColumn).Value
    Status = Status + 1
   
    Application.StatusBar = "Summarising " & Status & " of " & NumberOfSkills * NoOfEmployees & " Records" ' Shows user status
    
    Sheets("Summary_Data").Activate
    ActiveCell.Value = TeamMember
    ActiveCell.Offset(0, 1).Value = SkillStatus
    ActiveCell.Offset(0, 2).Value = SkillName
    ActiveCell.Offset(1, 0).Activate

    Sheets("Matrix (Status)").Activate
    ActiveCell.Offset(1, 0).Activate
   
    Next j
   
SkillColumn = SkillColumn + 1

Next i ' end of loop to bring skills over

Sheets("Summary Print").Activate
Range("A1").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

 

[BigDawg15]

Thank you.

BigDawg15