I have an excel spreadhseet where I am pulling in data from an external data source. I really would like to group and/or pivot the data to use for data validation.

What I want to do is take this:

NAME               |  COMPANY ID  
Abraham Lincoln    |  USG
Janet Jackson      |  JS5
John Kennedy       |  USG
Steve Jobs         |  APL

And make this:

      USG        |        JS5      |      APL
JOHN KENNEDY     |                 |

I've tried to use a pivot table, but I can't get figure out how to get the actual text to display in the "values" section for each column.

Using the "count" option for NAME, I can see where each name lands in each company, but I just can't figure out the last part of getting the names to display.

I need them to be in-column so I can use them for data validation. I also need the columns to be dynamically linked to the source table (hence the pivot table) so that anytime I refresh the query my pivoted data updates as well.

Any help or direction would be greatly appreciated. I am pulling in the external data using MS-Query. I would be open to pivoting data there as well. I tried to use pivot and group by commands in the SQL string but could not get them to work (I don't know if MS-Query supports such SQL commands).

Example of pivot table


THis can be done using the PowerQuery addin, or by using VBA and a workaround. Both approaches are detailed at https://stackoverflow.com/questions/32767117/pivot-in-excel-without-aggregation-to-show-text-not-numbers/

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.