How to pivot based on three fields?

[Jamal NUMAN]

How to pivot based on three fields?

For example, in the screenshots below, I wanted to pivot based on the following fields

To have F1 as rows
To have F2 as columns


To have F4 as values

Currently, it appears that excel has no power to write the values of F4 in the pivot table.

How this can be performed?


Thank you

Jamal

[ArviLaanemets]

You can't get cell values as Pivot Table values - they must be either Sum, Count, Average, Max, Min, Product, Numbers count, StdDev, StdDevp, Var or Varp of COLUMNS values.

As Pivot Table is 2-dimensional as max, with 3 fields you have to decide, do ROWS or COLUMNS have 2 fields to group. And as values you have to use Count - there is now way you can sum letters, and I can't see a point in summing F1 values too.

You can get the result more like you want, when you add a calculated column where you concatenate values in F1 and F2

[ArviLaanemets]

Here is an example how a Pivot table is created either with 3 columns, or with concatenated column

[Jamal NUMAN]

Originally Posted by ArviLaanemets

You can't get cell values as Pivot Table values - they must be either Sum, Count, Average, Max, Min, Product, Numbers count, StdDev, StdDevp, Var or Varp of COLUMNS values.

As Pivot Table is 2-dimensional as max, with 3 fields you have to decide, do ROWS or COLUMNS have 2 fields to group. And as values you have to use Count - there is now way you can sum letters, and I can't see a point in summing F1 values too.

You can get the result more like you want, when you add a calculated column where you concatenate values in F1 and F2

Thank you ArviLaanemets for the answer.

I’m not sure if there is other tool that can help performing deriving the result table out of the given table as shown in the screenshot below. It appears that Pivot tool has no such capacity

[ArviLaanemets]

I’m not sure if there is other tool that can help performing deriving the result table out of the given table as shown in the screenshot below

OK. In attachment is a possible solution which uses defined Tables, Table formulas, hidden rows and columns, and named ranges. Result table is on sheet Report.

On SourceTable sheet, columns D:H, containing intermediate calculations, are hidden.

On Report sheet, columns A:B with intermediate formulas are hidden. As in defined Tables column headers can't be formulas, dummy headers were created, and according F2 values are displayed at top as single datarow of additional table (with headers hidden).