Anura asks:

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A
Frank Branch A
Frank Branch A
Joe Branch A
Mary Branch B
Jane Branch C
Mike Branch A
Joe Branch A
Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

How can I count the number of transactions per person?

Answer:

First create a named dynamic range. If you add new values to your list the named range expands. This makes it easy to update your pivot table with newly added values. Then create a pivot table.

How to create a dynamic named range

This dynamic range formula adds both new columns and rows.

1. Click "Formula" tab on the ribbon
2. Click "Name Manager"
3. Click "New"
4. Type a name for the range, I named it "table"
5. Type this formula into "Refers to:" field:=OFFSET(Table!$A$1, 0, 0, COUNTA(Table!$A:$A), COUNTA(Table!$1:$1))
   
   
6. Click OK

The above table has the cell range $A$1:$C$11.

The dynamic range formula =OFFSET(Table!$A$1, 0, 0, COUNTA(Table!$A:$A), COUNTA(Table!$1:$1)) returns the same cell reference $A$1:$C$11.

How to create a pivot table in excel 2007

1. Select cell range A1:C11
2. Click "Insert" tab on the ribbon
3. Click "Pivot table" button

   

4. Type "table" in "Table/Range:" field
5. Select where you want the pivot table to be placed
6. Click OK

I placed the pivot table on a new sheet.

1. Drag and drop "Name" to "Drop Row Fields Here"
2. Drag and drop "Branch" to "Drop Column Fields Here"
3. Drag and drop "Amount" to "Drop Column Fields Here"

How to create a list of unique distinct values for branch A

1. Click Branch arrow
   
   
2. Deselect all but Branch A
3. Click OK!

How to count the number of transactions per person

Drag and drop "Name" into "Values". See picture below.

How to update pivot table with new values

1. Add more values to table
2. Right click Pivot table
3. Click "Refresh"

Download excel sample file for this tutorial.

unique-distinct-list-matching-criteria-pivot table.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

Related posts:

Extract a unique distinct list by matching items that meet a criterion in excel

Excel 2007 pivot table: Count unique distinct records (rows)

Change pivot table data source using a drop down list

List people with the highest scores based on criteria in a pivot table (Excel 2007)

Count unique distinct values in a pivot table in excel