Take the 2-minute tour ×

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.

Any idea's?

share|improve this question
Exact duplicate in stackoverflow. You should ask only in one place. –  DavidEG Oct 12 '11 at 18:08
Sorry, realized just after posting on stackoverflow that this isn't really a programming question, so I put it on here too –  Dylan Oct 12 '11 at 22:38
add comment

2 Answers

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!

share|improve this answer
add comment

I figured out that you can avoid the pivot table altogether. See the screenshot below. Use a helper column. Define the named ranges shown. Set up your layout as in G14:J18. In the body of the table, use VLOOKUP with a SUMPRODUCT.

enter image description here

share|improve this answer
add comment

Your Answer


