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 ABRAHAM LINCOLN | JANET JACKSON | STEVE JOBS 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
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).