excelexcel-2007excel-formula

How do I reformat an Excel list into matrix form automagically?

I have the following Excel worksheet:

  | A  B     C     
 -+---------------
 1|id  date  views
 2| 1  12-3  211
 3| 1  13-3  198
 4| 1  14-3  230
 5| 2  12-3   19
 6| 2  13-3   24
 7| 2  14-3   18
 8| 3  12-3   77
 9| 3  13-3   76
10| 3  14-3   98

The sheet gives the number of pageviews per date per website (identified by an integer). This is hard to read, however, so I want to transform the sheet into another format:

  | A    B    C   D  
 -+------------------
 1|date  1    2   3
 2|12-3  211  19  77
 3|13-3  198  24  76
 4|14-3  230  18  98

There's number of dates per website is always the same (3 in this example). There are no missing values. My question: how can I automate this (I now copy-pasting my ass off but it's getting ever more frustrating). I stared off by making an empty sheet like this:

  | A    B    C   D  
 -+------------------
 1|date  1    2   3
 2|12-3  
 3|13-3  
 4|14-3

What formules should I put in the cells so I can fill them automatically? Remember: in reality there are not 3 but hundered of dates and hundreds of websites so I can't type in a different formula in each cell :-) I started off with what is below, but if I "pull the cell down" I get the same C2 value (211) in all cells of column F.

  | E    F    G    H  
 -+-------------------
 1|date  1    2    3
 2|12-3  =C2  =C5  =C8
 3|13-3
 4|14-3

So the question would be what formulas to put in cell F2 (and perhaps G2 and H2) so that I can "pull them into the other cells" and the matrix fills itself with info from the C column?

Solution

  • Apply a Pivot Table to that data. Set the ID as the column source, set the Dates as the Row Source, and the VIEWS as the DATA source.

    1. Highlight the data
    2. Click on Insert > Pivot Table
    3. Selected range will aready be entered. Click [x]New Worksheet enter image description here

    4. Click OK and the new sheet will appear. Also onscreen you will see the PivotFieldList somewhere, looks something like this: enter image description here

    5. Lastly, drag and drop the fields above into the sections below to get the table layout the way you want. The table will be building itself on the sheet as you do this. enter image description here