Extract data to create numerous tables

[confused09]

In my worksheet (example below), I have a series of names of people who were
in receipt of payments (from 2003-2008) and each person has a unique
reference number. The payments are classified into two types, A and B. I want
to create a letter which is to be issued to each person, this letter will
include a table which gives a summary of the payments they received (see
table format below). Can anyone suggest how this may be accomplished? Many
thanks.

Worksheet:

Column A Column B Column C Column D Column E Column F
Reference Name Amount Commision Year Type of Payment
5566443A John Smyth $1000 $400 2006 A
5566443A John Smyth $2750 $975 2004 B
1235665D Paul Jones $600 $120 2008 B
7755661U Laura Doe $4200 $1600 2007 A
7755661U Laura Doe $2200 $750 2006 A
7755661U Laura Doe $1200 $460 2004 A
7755661U Laura Doe $3100 $1650 2003 B
4455894J Eric Gates $4000 $1900 2007 B
3388956R Erin Bright $1200 $200 2008 A
1222345L Mike Lee $5850 $3000 2007 B
1222345L Mike Lee $400 $85 2004 B
1222345L Mike Lee $1100 $655 2003 A


The letter will be laid out as follows:

Name:

Reference:

Table:

Year Type of Payment Amount Received Commission Charged
2008 Type A
Type B

2007 Type A
Type B

2006 Type A
Type B

2005 Type A
Type B

2004 Type A
Type B

2003 Type A
Type B

 

[Don Guillett]

Data>filter>autofilter>>>

 

[Jim Thomlinson]

A pivot table will do that for you without too much difficulty.

Select all of the data
Choose Data ->Pivot Table
A wizard will open
You can follow the wizard but generally I just hit finish
Place the year in the left column followed by the type
Place the amount received in the data section, followed by the commission.
Move the field called data by dragging it and placing it on top of the word
Total.
Place the names in the filter at the top.
You can format the data by selecting a format using the icon with a
lightning bolt in it.

To create all of the appropriate letters you will need to do a show pages.
How to do that depends on your version of XL. In 2000 or less right click the
filter and select show pages. In 2002 or better on the Pivot Table toolbar
slect PivotTable -> Show Pages.

 

[confused09]

Many thanks Jim, your solution has been very helpful.