Excel – Text values in a pivot table

microsoft excelpivot tablesql

I have a table (in MySQL) with 3 columns:

Location    Category     Supplier

   A        Computers    Company X
   A        Printers     Company Y
   B        Computers    Company X
   B        Printers     Company Y
   B        Software     Company Y
   C        Computers    Company Y
   C        Software     Company Z

Now I need to make a matrix containing the above information , like this :

       Computers      Printers       Software

A      Company X      Company Y
B      Company X      Company Y      Company Y
C      Company Y                     Company Z

Eventually I need to have this in Excel.

In reality I have a variable number of categories, so doing it in MySQL with a join for each column is not a good option. I could write a function in PHP, but I was wondering if there's a more elegant solution.

I looked a pivot tables in Excel, but they seem only suited for numbers as values. But maybe I'm overlooking something, since I never work with Excel myself.

Best Answer

I don't know if this is as "elegant" as you were hoping for, but you are on the right track with the pivot table idea. You just have to cheat a bit. A "helper column", a pivot table, and the "vlookup" function can be your friends here.

First, to the original data, add a helper column for a "SupplierNumber". Company X = 1, Company Y=2, Company Z=3. I put the helper column as the first column, for easy use later in a vlookup. Note the data is now in cells A5:D11.

enter image description here

Next, pivot the data like this. I used the "classic Pivot table layout" option in the "Display" tab of the "Pivot Table Options" dialog (right-click on the pivot table to bring that dialog up).

enter image description here

Select and copy the entire pivot table, then "paste special/values" in another location to the side of the original pivot table. Then, in the body of the table copy, enter the formula shown. Note that the vlookup formula is referring back to the original pivot table (the "H7" reference -- see the previous diagram) and also to the range A5:D11 mentioned earlier.

enter image description here

Like I said, it's not elegant, but it works. There are a lot of smart people that browse this site who will likely come up with an "elegant" solution for you!

Related Question