Extract a unique distinct list based on a condition [Pivot Table]
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.
- Click "Formula" tab on the ribbon
- Click "Name Manager"
- Click "New"
- Type a name for the range, I named it "table"
- Type this formula into "Refers to:" field:=OFFSET(Table!$A$1, 0, 0, COUNTA(Table!$A:$A), COUNTA(Table!$1:$1))
- 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
- Select cell range A1:C11
- Click "Insert" tab on the ribbon
- Click "Pivot table" button
- Type "table" in "Table/Range:" field
- Select where you want the pivot table to be placed
- Click OK
I placed the pivot table on a new sheet.
- Drag and drop "Name" to "Drop Row Fields Here"
- Drag and drop "Branch" to "Drop Column Fields Here"
- Drag and drop "Amount" to "Drop Column Fields Here"
How to create a list of unique distinct values for branch A
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
- Add more values to table
- Right click Pivot table
- Click "Refresh"
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
How to calculate totals of stock transactions based on dates
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
Disable autofit column widths for Pivot table
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.