From data to summary?
 

I want to prepare a simple bar chart so must first produce a summary.

https://dl.dropboxusercontent.com/u/...ataToTable.jpg

With about a thousand data rows as shown, what would be the simplest
way to do that please?

My pencil and paper approach (after sorting the data by Suffix - Year)
was straightforward enough, but I'm curious to know if there's an
elegant alternative.

--
Terry, East Grinstead, UK

From data to summary?
 

Hi Terry,

Am Thu, 10 Mar 2016 11:06:51 +0000 schrieb Terry Pinnell:

I want to prepare a simple bar chart so must first produce a summary.

https://dl.dropboxusercontent.com/u/...ataToTable.jpg

do you want to count the different Suffixes of all years?
Can't you write the Suffixes in E1:J1?
Then you could use in E2:
=COUNTIFS($A:$A,$D2,$B:$B,E$1)
and copy down and to right.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

From data to summary?
 

Hi Terry,

Am Thu, 10 Mar 2016 12:17:46 +0100 schrieb Claus Busch:

do you want to count the different Suffixes of all years?
Can't you write the Suffixes in E1:J1?
Then you could use in E2:
=COUNTIFS($A:$A,$D2,$B:$B,E$1)
and copy down and to right.

or an easier suggestion:
Select your data = Insert = PivotChart
Drag the years into the row range and the suffix into column range and
into values.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Excel Repair software is an advanced tool which is designed to repair and recover corrupt or damaged Excel documents. It recovers infected, corrupted or damaged Excel files. It also capable of extracting all corrupt data from partially and heavily damaged .xls and .xlsx files. To know more and download Excel Repair Tool from here- http://www.datarepairtools.com/excel-recovery.html

From data to summary?
 

Claus Busch wrote:

Hi Terry,

Am Thu, 10 Mar 2016 12:17:46 +0100 schrieb Claus Busch:

do you want to count the different Suffixes of all years?
Can't you write the Suffixes in E1:J1?
Then you could use in E2:
=COUNTIFS($A:$A,$D2,$B:$B,E$1)
and copy down and to right.

or an easier suggestion:
Select your data = Insert = PivotChart
Drag the years into the row range and the suffix into column range and
into values.


Regards
Claus B.

Thanks a bunch Claus. The second approach was what I was seeking and
it's introduced me to a feature I'd entirely forgotten. My two cols
of data is part of a large and complex spreadsheet with about 20
columns, so couldn;t use your first suggestion.

In Excel 2000 (and a few versions in the years after) it seems that
the menu route is under the Data menu, not Insert.

And it sure took some fiddling! Immediate obstacle is that I was
expecting the blank table to appear alongside my data, but it doesn't.
So dragging the data itself which is what I thought you (and the Excel
Help) meant was not possible. But it seems it's the wizard's buttons
that you drag.

Also, if I'd relied on the Help instead of your instructions I'd never
have realised that I had to drag the Suffix button into the main body
of the 'template' as well as into the Column area.

Maybe it's a feature that becomes easier with experience!

However, thanks to your post, happily I now have my result:
https://dl.dropboxusercontent.com/u/...aToTable-2.jpg

--
Terry, East Grinstead, UK