Excel Question

Factual Questions
1

I have a table I use to track player positions for my soccer team.

The table is formatted like:


Player  Q1  Q2    Q3   Q4
P1      K   K     SW   SW
P2      RF  RF    RF   OUT

etc.

What I want is a report, by quarter, of the number of players assigned to each position:



     K   SW   ST   RD   LD  LM  RM  RF  LF OUT
Q1   1   1    1    1    1   2   2   1   1  4
Q2   1   1    1    1    1   2   2   1   1  4

Doe anyone know how to use pivot tables to do this or some other slick way to generate such a report? It will help me figure out if I’ve assigned too many players to a given position for a given quarter.

2

You can use the countif function

=countif(x,y)

You’ll need to enter 4 formulas, 1 for each quarter

For x, enter the array corresponding to your first table for the quarter in question, making sure to use F4 to add dollar signs -> $B$2:$B$3

For y, enter the cell with the position K in it -> C8 for example (no $ here, or just on row, but not on column)

So, you’ll enter these four formulas under the K in your second chart shown above, and then drag them out to the right.

Then you’re done.

eta: you could get around having to enter 4 different formulas by building offsets into the countif function or by reversing the layout of the original chart, but it’s only 4 things, so whatever

3

It can be done fairly easily in a pivot table, but I’d suggest changing the position names to numbers–if for no other reason than it would make it easier to read. When you build the pivot table, you’ll put the positions into the “column labels” section, and the quarters into the “row labels” section of the pivot table field list. Changing “K” for 'keeper to “1”, “SW” for sweeper (?) to “2”, and so on stops you from having to specially sort them…otherwise you’d have to have a custom alphabetical sort.

Format the column values to “sum of items”, and you should be good to go.

4

Why would a numerical sort be any easier than alphabetical sort?

cmosdes: Which version of Excel are you running? MS decided to change how to set up a pivot chart when they went to 2007.

5

I’m hoping to use the data in the table to build the column headings, though. I could build a second table with the headings “K”, “SW”, etc., but since that data is already in original table, I want my pivot table to automagically build that. I can do this with a different stat program I have, but I want to be able to share this table with others who won’t have the stat program.

DukeHow do I put “K”, “SW”, etc. as the column labels? When I select the data area, I don’t see an option to say “these values are the labels”.

Munch2003 version.

6

It seems like typing in the headings would have taken less time than your post…

7

Absolutely. However, this really isn’t a question about how to this for this particular instance, as I had already found a way to do it by using 4 pivot tables. I was hoping to find a way to do it that will save me time the next time I need to do something similar but maybe that has 30 or 40 different categories instead of 9. I do appreciate the response, though. Your countif() option was not one I had previously known and it better than the solution I came up with.

8

Fair enough - I tend to prefer formulas over pivot tables for a bunch of reasons.

But really, if you want to deal with large amounts of data and outputting it in different forms (sorting by different cateories like you’re talking about), Access is a much more powerful tool.

9

Rather than start a second thread with the same title, I’ll just put this in here.

Take a look at this screenshot. Is there an easy way to remove blank cells, shifting all the "X"s to the left side of each row?

10

You would create a second chart on another sheet, and in cell A1 you would put this formula

=IF(COUNTIF(Sheet1!A$1:P$1,“X”)>COLUMN(A1)-1,“X”,"")

Then drag the forumla to cover the same cells as where the data is entered on the other sheet. Note the use of the $ signs. If your original sheet isn’t Sheet1, you’ll need to adjust as necessary.

This does require the use of a duplicated chart. If you want to actually move the Xs within the same chart, you’ll need to use a macro.

eta: the macro would just create a sheet set up like this, then copy as values over the original sheet, and then delete the sheet with the formulas