Is these a way to use a pivot table to count unique items in a set of data?

Thanks for you help.

Yes:

1) Ensure your source data is tabulated correctly:
- Contigous range with no blank rows/columns
- column headings etc.
2) Insert Pivot table
3) Place the field you want to count unique in the 'ROW'
section of the pivot table

The resulting display will be a unique list of items from that
particular column, so the number of items in the list *is* your unique
count.

Jay
--



Jordan wrote:
Is these a way to use a pivot table to count unique items in a set of data?

Thanks for you help.

Jason, Thanks for the quick response. I did what you suggested. Using a
simple example:

Name Amount
Cindy 1
Cindy 2
Ann 3
Jordan 4
Jordan 5

The results in the row show;

Ann
Cindy
Jordan

Which is correct, it is only showing three items. Obvious to see in this
example but I have hundreds of rows and need a count of unique items. If I
put the name in the data area of the pivot table and ask for a count I will
get 2 for Cindy 1 for Ann and 2 for Jordan, showing the total number of times
I have a name listed not the unique value.

Thanks again for your help.


"Jason" wrote:

Yes:

1) Ensure your source data is tabulated correctly:
- Contigous range with no blank rows/columns
- column headings etc.
2) Insert Pivot table
3) Place the field you want to count unique in the 'ROW'
section of the pivot table

The resulting display will be a unique list of items from that
particular column, so the number of items in the list *is* your unique
count.

Jay
--



Jordan wrote:
Is these a way to use a pivot table to count unique items in a set of data?

Thanks for you help.

Jordan, why are you using a pivot table? Is your goal just to get a
count of unique items? If so, use the following formula:

=SUM(IF(FREQUENCY(IF(LEN(A1:A200)0,MATCH(A1:A200, A1:A200,0),""),
IF(LEN(A1:A200)0,MATCH(A1:A200,A1:A200,0),""))0, 1))

and CSE array enter it (Ctrl,Shift,Enter). This will count unique
items, whether numbers or text.

One way of including a unique count in a pivot table is to add a 'helper
column' to your underlying data. Insert a new column (called 'Unique')
after the column containing the items you want to uniquely count, and
put the following formula in the top-most cell and auto-fill the formula
down the rest of the column:

=IF(COUNTIF($A$5:A5,A5)1,"",1)

Where A5 is the first row in the column of values you want to count
unique. When copied down this formula will put a 1 against every unique
item. Then you can add this 'Unique' column to the DATA section of the
pivot and both SUM and COUNT will give you a count of unique items in
column A of your data.

HTH,

Jay
__




Jordan wrote:
Jason, Thanks for the quick response. I did what you suggested. Using a
simple example:

Name Amount
Cindy 1
Cindy 2
Ann 3
Jordan 4
Jordan 5

The results in the row show;

Ann
Cindy
Jordan

Which is correct, it is only showing three items. Obvious to see in this
example but I have hundreds of rows and need a count of unique items. If I
put the name in the data area of the pivot table and ask for a count I will
get 2 for Cindy 1 for Ann and 2 for Jordan, showing the total number of times
I have a name listed not the unique value.

Thanks again for your help.


"Jason" wrote:

Yes:

1) Ensure your source data is tabulated correctly:
- Contigous range with no blank rows/columns
- column headings etc.
2) Insert Pivot table
3) Place the field you want to count unique in the 'ROW'
section of the pivot table

The resulting display will be a unique list of items from that
particular column, so the number of items in the list *is* your unique
count.

Jay
--



Jordan wrote:
Is these a way to use a pivot table to count unique items in a set of data?

Thanks for you help.

Excel 2007 PivotTable
Count unique items.
No helper column in source data.
No formulas.
No code.
http://www.mediafire.com/file/ymefzfonvnj/07_25_09.xlsx