Author: Oscar Cronquist Article last updated on December 30, 2018

Anura asks:

I have a list of credit card transactions showing the name of the cardholder, 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. Press with left mouse button on "Formula" tab on the ribbon
  2. Press with left mouse button on "Name Manager"
  3. Press with left mouse button on "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. Press with left mouse button on 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. Press with left mouse button on "Insert" tab on the ribbon
  3. Press with left mouse button on "Pivot table" button
  4. Type "table" in "Table/Range:" field
  5. Select where you want the pivot table to be placed
  6. Press with left mouse button on 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. Press with left mouse button on Branch arrow

  2. Deselect all but Branch A
  3. Press with left mouse button on 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. Press with right mouse button on Pivot table
  3. Press with left mouse button on "Refresh"

Get the Excel file


unique-distinct-list-matching-criteria-pivot-table.xls

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Extract dates from a cell block schedule

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

How to save custom functions and macros to an Add-In

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

Sum unique distinct invoices

Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]

Lookup and match last value – reverse lookup

This article shows a formula that performs a reverse lookup and returns the corresponding value based on the last matching […]

Count entries based on date and time

Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]

Analyze trends using pivot tables

Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]