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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
221
Office_Drone
O
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
512
ItalianPlatinum
I
F
  • Solved
Loaded question with filter function multiple criteria and return value.
Replies
2
Views
238
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,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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