Excel pivot table: Show values instead of summaries

I have a table of data like this:

ID  |Name   |Brand  |Model  |License
------------------------------------
1   |Foo    |KOM    |830 AC |A
1   |Foo    |KOM    |930 E  |B
1   |Foo    |CAT    |980 H  |B
2   |Bar    |KOM    |930 E  |A
2   |Bar    |CAT    |980 H  |A
3   |Baz    |P&H    |280 XB |B

The ID and Name are tied together(ID 1 is always Foo), also there is never more than one license for a pair of Name and Model)

I want to use a pivot table to turn it into this:

    |       |CAT    |KOM            |P&H
ID  |Name   |980 H  |830 AC |930 E  |280 XB
-------------------------------------------
1   |Foo    |B      |A      |B      |
2   |Bar    |A      |       |A      |
3   |Baz    |       |       |       |B

This is as far as I've gotten:

But:

• Ids and Names are in different rows


• It shows the count (always 1) of licenses instead of showing the values

Is there a way to make it work the way I want?

Answer

Unfortunately, pivot tables can't return text. They aggregate.

However, there are workarounds. The simplest involves a "Helper" column and VLOOKUP. See my screenshot below.

First, insert a "Helper" column just to the left of your "License" column that concatenates the ID, Name, Brand and Model.

Next, construct the same pivot table as before, but select the "classic view" so that your layout is identical to your 2nd screenshot. Select the entire pivot table, then "copy, paste special, values". This will preserve the pivot table layout but remove the pivot table functionality.

Finally, use a VLOOKUP as indicated. Pay close attention to the $ sign placements in the formula so that the absolute references are preserved. The screenshot shows the formula in cell S13; copy it over and down as needed.

(In the pivot table layout, you'll also need to fill in the blanks in the "Brand" row. In the screen shot below, there's a formula in cell U11: = T11).

Admittedly, if your source data expands, you'll need to extend this technique.

There are other methods involving tables, INDEX+MATCH and array formulas.