Layout help for Pivottable - Yes/No from multiple questions

T

trikky

New Member
Joined
Dec 28, 2016
Messages
32
I have a substantial database with multiple entries for employee evaluations – each entry showing YES/NO/NA responses for up to 40 individual questions.

Currently, I have a summary spreadsheet with various formulas (sumproduct, etc) that presents results based on selected employee name and months, but due to the size of the data, it’s relatively slow when doing calculations. I am thinking a pivottable would enable me to present the data faster on my summary while enabling me to use slicers to drill down a little more.

Here is a sample of my data.

[TABLE="width: 777"]
<tbody>[TR]
[TD="width: 205, bgcolor: #4F81BD"]Agent

[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Topic
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Date
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]ID
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q1
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q2
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q3
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q4

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 1
[/TD]
[TD="bgcolor: #DCE6F1"]Topic 1
[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2018-10-18
[/TD]
[TD="bgcolor: #DCE6F1"]AB
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 2
[/TD]
[TD="bgcolor: transparent"]Topic 2
[/TD]
[TD="bgcolor: transparent, align: right"]2018-10-12
[/TD]
[TD="bgcolor: transparent"]BC
[/TD]
[TD="bgcolor: transparent"]No
[/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 3
[/TD]
[TD="bgcolor: #DCE6F1"]Topic 3
[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2018-10-01
[/TD]
[TD="bgcolor: #DCE6F1"]AD
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 1
[/TD]
[TD="bgcolor: #DCE6F1"]Topic 1
[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2018-10-18
[/TD]
[TD="bgcolor: #DCE6F1"]BF
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[TD="bgcolor: #DCE6F1"]N/A
[/TD]
[TD="bgcolor: #DCE6F1"]Yes
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 2
[/TD]
[TD="bgcolor: transparent"]Topic 2
[/TD]
[TD="bgcolor: transparent, align: right"]2018-10-12
[/TD]
[TD="bgcolor: transparent"]QZ
[/TD]
[TD="bgcolor: transparent"]N/A
[/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"]No
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 3
[/TD]
[TD="bgcolor: #DCE6F1"]Topic 3
[/TD]
[TD="bgcolor: #DCE6F1, align: right"]2018-10-01
[/TD]
[TD="bgcolor: #DCE6F1"]ZY
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[TD="bgcolor: #DCE6F1"]N/A
[/TD]
[TD="bgcolor: #DCE6F1"]No
[/TD]
[TD="bgcolor: #DCE6F1"]N/A
[/TD]
[/TR]
</tbody>[/TABLE]



This is what I’m trying to accomplish (or something similar).

[TABLE="width: 777"]
<tbody>[TR]
[TD="width: 205, bgcolor: #4F81BD"]Agent
[/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q1
[/TD]
[TD="width: 118, bgcolor: #4F81BD"][/TD]
[TD="width: 118, bgcolor: #4F81BD"][/TD]
[TD="width: 118, bgcolor: #4F81BD"]Q2
[/TD]
[TD="width: 118, bgcolor: #4F81BD"][/TD]
[TD="width: 118, bgcolor: #4F81BD"][/TD]
[TD="width: 118, bgcolor: #4F81BD"][/TD]
[/TR]
[TR]
[TD="bgcolor: #4F81BD"][/TD]
[TD="bgcolor: #4F81BD"]Yes
[/TD]
[TD="bgcolor: #4F81BD"]No
[/TD]
[TD="bgcolor: #4F81BD"]N/A
[/TD]
[TD="bgcolor: #4F81BD"]Yes
[/TD]
[TD="bgcolor: #4F81BD"]No
[/TD]
[TD="bgcolor: #4F81BD"]N/A
[/TD]
[TD="bgcolor: #4F81BD"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 1
[/TD]
[TD="bgcolor: #DCE6F1"]2
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]2
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Employee 2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]Employee 3
[/TD]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
</tbody>[/TABLE]


I’m not worried about which labels are on the X axis, and which are on the Y. Additionally, I don’t need all employees listed in the pivottable, as long as the end result is the YES/NO/NA count for each question, for the employees I have selected in the slicers.

My attempts to figure out what goes in the values, columns, and rows fields has wound up to be quite messy and certainly does not result in anything close to my desired output, so I thank you for any assistance you can offer on what data field should go where.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Agent[/td][td=bgcolor:#5B9BD5]Topic[/td][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Q1[/td][td=bgcolor:#5B9BD5]Q2[/td][td=bgcolor:#5B9BD5]Q3[/td][td=bgcolor:#5B9BD5]Q4[/td][td][/td][td=bgcolor:#DDEBF7]Count of Value[/td][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7]Value[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Employee 1[/td][td=bgcolor:#DDEBF7]Topic 1[/td][td=bgcolor:#DDEBF7]
18/10/2018​
[/td][td=bgcolor:#DDEBF7]AB[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Yes[/td][td][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q1[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q2[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q3[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Q4[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Employee 2[/td][td]Topic 2[/td][td]
12/10/2018​
[/td][td]BC[/td][td]No[/td][td]Yes[/td][td]Yes[/td][td]N/A[/td][td][/td][td=bgcolor:#DDEBF7]Agent[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]N/A[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]N/A[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]N/A[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]N/A[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Employee 3[/td][td=bgcolor:#DDEBF7]Topic 3[/td][td=bgcolor:#DDEBF7]
01/10/2018​
[/td][td=bgcolor:#DDEBF7]AD[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td=bgcolor:#DDEBF7]No[/td][td][/td][td]Employee 1[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td]
2​
[/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Employee 1[/td][td]Topic 1[/td][td]
18/10/2018​
[/td][td]BF[/td][td]Yes[/td][td]No[/td][td]N/A[/td][td]Yes[/td][td][/td][td]Employee 2[/td][td][/td][td]
1​
[/td][td]
1​
[/td][td]
2​
[/td][td][/td][td][/td][td]
2​
[/td][td][/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Employee 2[/td][td=bgcolor:#DDEBF7]Topic 2[/td][td=bgcolor:#DDEBF7]
12/10/2018​
[/td][td=bgcolor:#DDEBF7]QZ[/td][td=bgcolor:#DDEBF7]N/A[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]Yes[/td][td=bgcolor:#DDEBF7]No[/td][td][/td][td]Employee 3[/td][td]
1​
[/td][td]
1​
[/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][td][/td][td]
2​
[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Employee 3[/td][td]Topic 3[/td][td]
01/10/2018​
[/td][td]ZY[/td][td]No[/td][td]N/A[/td][td]No[/td][td]N/A[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

if yes, use PowerQuery

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source
 
Upvote 0
sandy666 said:
is that what you want?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Agent[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Topic[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q1[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q2[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q3[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Q4[/COLOR]
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Count of Value
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Attribute
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Value
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Employee 1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Topic 1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
18/10/2018​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]AB
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Q1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Q2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Q3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Q4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Employee 2
[/TD]
[TD]Topic 2
[/TD]
[TD]
12/10/2018​
[/TD]
[TD]BC
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Agent
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]N/A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]N/A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]N/A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]N/A
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Employee 3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Topic 3
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
01/10/2018​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]AD
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD][/TD]
[TD]Employee 1
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Employee 1
[/TD]
[TD]Topic 1
[/TD]
[TD]
18/10/2018​
[/TD]
[TD]BF
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]Employee 2
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Employee 2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Topic 2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]
12/10/2018​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]QZ
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]N/A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Yes
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]No
[/TD]
[TD][/TD]
[TD]Employee 3
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Employee 3
[/TD]
[TD]Topic 3
[/TD]
[TD]
01/10/2018​
[/TD]
[TD]ZY
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


if yes, use PowerQuery

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source
Click to expand...

Thanks. Now, the only problem here is that I have never used PowerQuery so don't know where to put that. Additionally of course have no idea exactly what the code translates to, so don't know how I can modify it to suit my actual workbook fields.

Just upgraded (at the office) to Office365 so it's Excel 2016.
 
Upvote 0
sandy666 said:
is that what you want?

[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Agent[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Topic[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Date[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]ID[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q1[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q2[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q3[/COLOR]
[/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]Q4[/COLOR]
[/TD]
[TD][/TD]
[TD]Count of Value
[/TD]
[TD]Attribute
[/TD]
[TD]Value
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]Topic 1
[/TD]
[TD]
18/10/2018​
[/TD]
[TD]AB
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Q4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]Topic 2
[/TD]
[TD]
12/10/2018​
[/TD]
[TD]BC
[/TD]
[TD]No
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD]Agent
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]Topic 3
[/TD]
[TD]
01/10/2018​
[/TD]
[TD]AD
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD]Employee 1
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 1
[/TD]
[TD]Topic 1
[/TD]
[TD]
18/10/2018​
[/TD]
[TD]BF
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]Employee 2
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]Employee 2
[/TD]
[TD]Topic 2
[/TD]
[TD]
12/10/2018​
[/TD]
[TD]QZ
[/TD]
[TD]N/A
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD]Employee 3
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]Employee 3
[/TD]
[TD]Topic 3
[/TD]
[TD]
01/10/2018​
[/TD]
[TD]ZY
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD]No
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


if yes, use PowerQuery

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Agent", type text}, {"Topic", type text}, {"Date", type datetime}, {"ID", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"Q4", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Topic", "Date", "ID"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Agent"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source
Click to expand...

Okay, so I've used thePowerQuery wizard (thanks to the magic of Google) to extract the columns I need(which is pretty much all of columns from the original data source, but the extracted data is essentially the same…so of course the end result is that I'm at thesame place with the pivottable construction.

The default code that PowerQuery gave me when extracting the columns I needed (following the steps in a Microsoft PowerQuery tutorial) looks nothing like that you provided.

Without knowing what your code is doing and what the parameters are, I'm afraid it's just Greek to me and doesn't help me figure out how to get the data to present the way I am hoping.

Thanks for your followup.
 
Last edited:
Upvote 0
sandy666 said:
maybe it will help : Movie how to... (it should play on every kind of Windows)

excel file

btw. M-code you can see in PowerQuery editor - Advanced Editor
Click to expand...

Had a quick look at the video when I got home - I'll take a closer look when I'm at the office again, with the spreadsheet in front of me.

Appreciate the effort you went to, to assist. I can see a couple of areas where I went slightly off track.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Helpful formatting/delimeters for a multi-use text box?
Replies
4
Views
230
Office_Drone
O
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
516
ItalianPlatinum
I
F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
249
floW_5
F
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
F
  • Solved
Adding up values in one table based on values in another table
Replies
2
Views
1K
formula911
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top