Problem in the pivot table

[Yaghoub61]

Hi all,

I have a table with 3 columns:

Name Day Hour
A Monday 8
B Monday 10
C Tuesday 8
D Wednesday 8
E Monday 11
F Tuesday 9
G Wednesday 11

I am using a pivot table

Row tables: Hour
column tables: Day
Values: Name

Result: values: 1,1,1,.... I do not want this result


Result: values: A,B,C,... I want this result

Is it Possible?

Many Thanks for help me!

[bluesky63]

Hi,

Using conditional formatting to do it, a bit primitive and if you got a lot of mapping then will be tedious

Insert a mapping ID A=1, B=2, C=3 ........ then create rules for each character in conditional formatting,
you need to off the grand totals for row and column and use count formulae


if you have excel 2013 and above, perhaps using Power Query is a better way if you really wanted such presentation

Rgds

[Yaghoub61]

Superb!

BlueSky63, your job was so good!

Thanks a lot!

[bluesky63]

Use Power query will be easier

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Columns1" = Table.RemoveColumns(Source,{"ID"}),
#"Grouped Rows1" = Table.Group(#"Removed Columns1", {"Hour"}, {{"Grouped", each _, type table [Name=text, Day=text, Hour=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.RowCount([Grouped])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Count Name"}}),
#"Expanded Grouped" = Table.ExpandTableColumn(#"Renamed Columns", "Grouped", {"Name", "Day"}, {"Name", "Day"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Grouped",{{"Count Name", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Day]), "Day", "Name"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Hour", "Monday", "Tuesday", "Wednesday", "Count Name"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Hour", type number}})
in
#"Changed Type1"