Horizontal and Vertical filter

[Gvlewis81]

Hi All,

it's been awhile since i've posted here and always found it so helpful.

I have a table similar to below. Vertically it has a list of subjects. Horizontally it has peoples names. Is there a way of being able to do this in a pivot table? I want to create a Radar graph where i can see everyone, or people individually. There are two types activity in the table with multiple Activities. The scoring is a capability score. So i'd like to be able to filter the type of activity, the activity itself and then by the person.

I'm really basic with pivot tables and they do blow my mind a bit!

Help appreciated.

Gareth

Type of Activity	Activiy	Person 1	Person 2	Person 3
Technical	Be able to do this	3	3	5
Technical	Be able to do that	3	4	3
Leadership	Be able to do the other	3	3	3
Leadership	Be able to do some more	3	2	4

 

[mrshl9898]

To Pivot it you'd need to transform the data like this:

Type of Activity	Activity	Person	Number
Technical	Be able to do this	Person 1	3​
Technical	Be able to do this	Person 2	3​
Technical	Be able to do this	Person 3	5​
Technical	Be able to do that	Person 1	3​
Technical	Be able to do that	Person 2	4​
Technical	Be able to do that	Person 3	3​
Leadership	Be able to do the other	Person 1	3​
Leadership	Be able to do the other	Person 2	3​
Leadership	Be able to do the other	Person 3	3​
Leadership	Be able to do some more	Person 1	3​
Leadership	Be able to do some more	Person 2	2​
Leadership	Be able to do some more	Person 3	4​

This code should do it (bit rusty but tested ok)

Sub TransormData()

Dim sht As Worksheet
Dim colnum As Long
Dim LastRow As Long
Dim LastRow2 As Long
Dim FirstRow As Long
Dim FirstRow2 As Long
Dim RowCount As Long

Set sht = ActiveSheet

Columns("C:C").Insert Shift:=xlToRight, _
    CopyOrigin:=xlFormatFromLeftOrAbove '
    
colnum = 4

Range("C1") = "Person"

FirstRow = 2
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("C" & FirstRow & ":C" & LastRow).Value = Cells(1, colnum)
RowCount = LastRow - FirstRow + 1
colnum = 5


Do Until Cells(1, colnum) = ""
Range("A" & FirstRow & ":B" & LastRow).Copy Range("A" & sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1)

LastRow2 = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
FirstRow2 = LastRow2 - RowCount + 1
Cells(1, colnum).Copy Range("C" & FirstRow2 & ":C" & LastRow2)
Range(Cells(FirstRow, colnum), Cells(LastRow, colnum)).Copy Range("D" & FirstRow2 & ":D" & LastRow2)
colnum = colnum + 1
Loop

Range("E:EZ").ClearContents
Range("D1").Value = "Score"



End Sub

 

[alansidman]

An alternative to getting the data ready for Pivot Table is to employ Power Query. Here is the Mcode that produces the same result as shown below which is the same as Post #2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type of Activity", type text}, {"Activiy", type text}, {"Person 1", Int64.Type}, {"Person 2", Int64.Type}, {"Person 3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Type of Activity", "Activiy"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book7
	A	B	C	D
1	Type of Activity	Activiy	Attribute	Value
2	Technical	Be able to do this	Person 1	3
3	Technical	Be able to do this	Person 2	3
4	Technical	Be able to do this	Person 3	5
5	Technical	Be able to do that	Person 1	3
6	Technical	Be able to do that	Person 2	4
7	Technical	Be able to do that	Person 3	3
8	Leadership	Be able to do the other	Person 1	3
9	Leadership	Be able to do the other	Person 2	3
10	Leadership	Be able to do the other	Person 3	3
11	Leadership	Be able to do some more	Person 1	3
12	Leadership	Be able to do some more	Person 2	2
13	Leadership	Be able to do some more	Person 3	4
Sheet2