Re: Variable number of multiple rows per record; want summary by record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

TR
It looks like the sort of thing a pivot table could handle. It isn't
clear, tho, what the two columns after the names represent. Are they
different things (Wins and Losses) or the same (Bananas)?

Assuming that they are different, give each column a header. (eg Name,
Win, Loss)
Select all rows & columns with data (including header)
Data>PivotTable Report...
Step 1 - where is data? choose Excel list or database
Step 2 - should already show the range containing the data
Step 3 - where to put table, choose New Worksheet
click on Layout
Drag the "Name" label to the Row box
Drag the "Win" label to the Data box. It will say "sum of Win". Double
click "sum of Win"
Choose Average instead of sum. Format if you want.
Do same with "Loss".
Ok. Finish.

There are more bells and whistles, but this is the basic idea.
Just don't trust the "standard deviation" option (see my recent post).

If ColB and ColC are really the same sort of quantity (bananas), it can
still be done, but is a bit trickier. You have to display COUNT of each
column in the pivot table and do some math to find the overall average.
Too complicated for this post.

jpdphd

TR wrote:
Here's what the data look like

Pat 10 20
Joe 15 10
Joe 8 11
Kay 9 32
Pat 14 17
Joe 33 11

In short, each person has from 1 to n rows of data.. I want a summary
(probably on a different sheet) that shows the mean for each person.
The new sheet will have one row per person, showing the mean for each
data column for that person.

Suggestions?

TIA

.

Relevant Pages

  • Data sorting problem
    ... I have a pivot table which draws data from a sheet in my workbook that is ... Column K has data in it that looks like this before the sort: ...
    (microsoft.public.excel.programming)
  • Re: maximum number of worksheets
    ... i saw your other post and i think you should have a look at pivot tables - ... >I have been trying to put all my data in one work sheet and then extract ... >> sort, pivot table functions ...
    (microsoft.public.excel.worksheet.functions)
  • Re: customised printing of multiple pivot charts & tables - unsure how to progress
    ... > You could have the code in your userform format the sheet by hidding rows ... > sheet and doing a ActiveWindow.SelectedSheet.PrintOut. ... >> I could probably sort out a for...next loop to print each one in turn, ... >> pivot table changed, this would also be picked up)? ...
    (microsoft.public.excel.programming)
  • Pivot Table not in order - why?
    ... The pivot table is looking at MAR to be an abbreviation ... >data that includes a plant code column. ... >sorted by plant code on one sheet (which shouldn't make ... >Any idea what is causing MAR to sort first? ...
    (microsoft.public.excel.programming)
  • Re: Sorting columns in a Transform/crosstab query
    ... Hash: SHA1 ... I really don't recommend changing the table structure just to get a ... sort the column headers by project name within a project type. ... You can't sort the PIVOT values and it doesn't matter if the source is ...
    (microsoft.public.access.queries)