5

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:

Pivot Table

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?

1

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.

enter image description here

0
0

ok, as you can see from this question, you can transpose 1 column of values against another with the following steps:

  • Open your table in the Power Query editor
    • make the data into a table
    • select the table
    • select data -> from table/range
  • Use Power Query to make the transposition
    • select the Subj and Course columns
    • Select the Transform tab:
    • Select Pivot Column, you may have to hover to find it
    • Values column = Course Select
    • advanced options -> don't aggregate
  • Bring it back to your spreadsheet
    • Home tab (still in Power Query)
    • Get Data -> Close and load ..

Looks like this

To get the complete answer, you'd then need to add brand to the header row

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.