Home > Pivot > Unique > No Data Model

Count Unique Items in Pivot Table No Data Model

Debra Dalgleish - Contextures

In Pivot Table A, shown below, there are 8 unique sales reps listed for the East region. In Excel 2010 pivot tables, there isn't a built-in unique count, so you can use a second pivot table (B) instead.

Note: For Excel 2013, and later versions, I recommend the data model unique count method.

pivot table shows 8 unique sales reps in east region

Contents

-- Pivot Table Unique Count

-- Build Two Pivot Tables

-- Download Sample Workbooks

-- Related Tutorials

Pivot Table Unique Count

To get a unique count of salespeople per region in Excel 2010, you can use two pivot tables, instead of one.

  1. First, create a pivot table summary
  2. Next, create a second pivot table, based on the first one.

Thanks to Roger Govier, who shared this technique and workbook.

Product Sales Data

In this example, there is a named Excel table, with product sales records. Each record shows the region and salesperson name, and other data.

Note: You can get the sample file in the Download section below.

source data for pivot

Build Two Pivot Tables

The four steps below show how to build the two pivot tables, and get a distinct (unique) count for the Person field

Step 1) Create First Pivot Table

The first pivot table will be set up to look like a normal table of data.

To create the first pivot table, follow the steps below:

  • Create a Pivot Table from the sales data, with Region and Person in the Rows area
  • Add Units and Value in the Values area
    • Because Person is a text field, it will automatically show as "Count of"
  • Format the pivot table with the Tabular report layout
  • Set all the Item labels to repeat in each row.
  • Change the column headings, to remove the "Sum of"
    • Tip: Type the original field name, with a space character at the end
  • (Optional) Name the pivot table, PT_A

pivot table shows 8 unique sales reps in east region

Step 2) Name Pivot Table Range

In Step 4, you'll add a macro to your workbook, to refresh the pivot tables automatically. That macro creates a named range, myData, where the first pivot table (PT_A) is located.

For now, follow the quick steps below to manually create that named range:

  • In the first pivot table (PT_A), select the Region heading cell
  • Press the keyboard shortcut, Ctrl+A, to select all the heading and data cells
  • Next, click in the Name box, at the left of the Formula bar
  • Type a one-word name for the cell -- myData
  • Finally, press Enter, to complete the name

Note: If you don't want to use macros, go to the No Macros section. There are steps for manually setting up the myData named range.

Step 3) Create Second Pivot Table

Next, follow the steps below, to create the second pivot table:

  • Insert a new Pivot Table, based on the myData named range
  • In the empty pivot table, put Region in the Row area
  • Put Person, Units and Value in the Values area
  • (Optional) Name the pivot table, PT_B
  • (Optional) Remove "Count of" and "Sum of" from the headings

Because it is based on the summarized data in the first pivot table (PT_A), this pivot table (PT_B) shows the count of unique salespeople per regions.

In the screen shot below, the East region shows a count of the 8 unique salespeople's names.

pivot table shows unique count per region

Step 4) Refresh Pivot Tables

Any time you change the source data, you need to refresh both pivot tables, manually, or with a macro.

  • Macro: I recommend using the following macro for this, so the pivot tables automatically show the latest data from the source table.
  • No Macro: If you don't want to use macros, go to the No Macros section. There are steps for manually refreshing the pivot tables.

4a) Add Pivot Refresh Macro

To add the macro to your workbook, follow the steps below:

  • First, copy the macro code shown in the box below (select all the text, and press Ctrl+C )
  • Next, in your workbook, right-click the tab for sheet where the second pivot table (PT_B) is located.
  • In the pop-up menu, click View Code
  • In the code module that opens, right-click where the cursor is flashing
  • Click the Paste command
Macro Code
Private Sub Worksheet_Activate()
Dim Sh1Name As String
Dim Pt1Name As String
Dim Pt2Name As String

'sheet where 1st pivot located
Sh1Name = "TwoPivots"
Pt1Name = "PT_A"   'first pivot
Pt2Name = "PT_B"   'second pivot

Sheets(Sh1Name).PivotTables(Pt1Name) _
  .PivotCache.Refresh

ThisWorkbook.Names.Add _
    Name:="myData", _
    RefersTo:=Sheets(Sh1Name) _
        .PivotTables(Pt1Name) _
        .TableRange1

Me.PivotTables(Pt2Name) _
  .PivotCache.Refresh
  
End Sub

4b) Complete the Code

To complete the code, follow these two steps:

  • First, if your workbook has different pivot table names, and sheet name, change the code to use your workbook's names.
  • After that, press the keyboard shortcut, Alt+Q, to close the Visual Basic Editor, and return to Excel.

4c) Test the Code

After you follow the steps below, to add the macro code, the two pivot tables will refresh automatically, any time you go to the second pivot table's sheet.

To test the code, follow these steps:

  • Go to the source data table
  • Make a big change to one of the numbers. For example, change the first Unit from 1 to 10000
  • Go to the second pivot table sheet
  • The pivot tables should automatically update, and the larger number of units shows in the Units column

Two Pivots - No Macros

If you don't want macros in your workbook, follow the steps below to:

  • A) Use a formula to create the myData named range
  • B) Refresh both pivot tables manually, in correct sequence

A) Create myData Named Range

The myData named range includes all the headings and data in the main part of the first pivot table (PT_A)

For a no-macro solution, follow the 3 steps below, to set this up:

Note: The myData named range will update automatically, after you refresh the first pivot table.

Step 1) Name Pivot Start Cell

First, follow these steps to name the first cell in the pivot table.

  • In the first pivot table (PT_A_, select cell B2 - heading cell in the pivot table's Region column
  • Next, click in the Name box, at the left of the Formula bar
  • Type a one-word name for the cell -- pvtStart
  • Finally, press Enter, to complete the name

Step 2) Name Pivot End Row Number

Next, follow these steps to name a cell that calculates the last row number in the pivot table:

  • On the Data sheet, in cell M2, enter this formula:
    • =MATCH(9.9E+307,TwoPivots!D:D,1)
  • Next, select cell M2, and in the Name Box, type PvtEnd, and press Enter.
How the Formula Works

In the MATCH function, there are 3 arguments:

  1. First argument is a very large number, written in scientific notation -- 9.9E+307
  2. Second argument is where to look for that number -- worksheet named TwoPivots, in column D (Units)
  3. Third argument is 1 (Less Than)

All of the numbers in column D are less than the large number, so MATCH returns the row number of the last number in column D

Note: This formula will only work in a column with numbers.

Step 3) Name Pivot Table Range

Next, follow the steps below, to create a dynamic named range, based on the pivot table headings and data.

This range will start in Region heading cell (pvtStart), and end in the last row in column E (PvtEnd).

  • On the Ribbon's Formulas tab, click Define Name
  • In the New Name dialog box, enter the name, myData
  • In the Scope drop down, select Workbook
  • In the Refers To box, type this INDEX formula:
    • =pvtStart:INDEX(TwoPivots!$E:$E, PvtEnd)
  • Click OK, to create the named range

pivot table

B) Manually Refresh Pivot Tables

After you make changes to the source data, be sure to refresh both pivot tables, in the correct sequence:

  1. Always refresh the first pivot table first
    • The named range, myData, will automatically adjust
  2. After that, refresh the second pivot table

Other Ways to Get Unique Count

Here are three more ways to get the number of unique values in an Excel pivot table. The sample files for the COUNTIF and Power Pivot methods are in the download section below.

Data Model: For Excel 2013, and later versions, I recommend the data model unique count method.

COUNTIF: In Excel 2007 and older versions of Excel, add a column to the source data, and use the COUNTIF function. This method can cause the workbook to calculate slowly.

Power Pivot: If the Power Pivot add-in is installed, use it to show a count of distinct values.

Get the Sample Files

  • 2 Pivots - No Macros: Download the sample Excel workbook, which contains the sample data and pivot tables. The file is zipped, and in xlsb format. The file does not contain macros.
  • 2 Pivots - Macros: Download the sample Excel workbook, which contains the sample data and pivot tables, and the macros to refresh the pivot tables. The file is zipped, and in xlsb format. The file contains macros, so be sure to enable them when you open the file.
  • COUNTIF: For Excel 2007, which doesn't have "Repeat Labels", you can download the COUNTIF technique sample file. The file is zipped, and in xlsx format, and does not contain macros.
  • Power Pivot: To follow along with the Power Pivot video, download the sample file that was used for this video

Where to Go Next

Unique Count Excel 2013

Calculated Items vs Calculated Fields

Calculated Item Examples

Calculated Field - Count

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at: roger@technology4u.co.uk

Roger Govier Roger Govier

Technology 4 U

 

 

Last updated: April 7, 2024 10:04 AM