Hi,
I have a large list - and was wondering how to count unique numbers (eg.
customer IDs) in a pivot table.
I have arranged my data so each location is shown in the rows.
Can this be done? So far I can only get the data field to count the total
entries, but not unique entries by location.
If not - is there a way to do this with a formula?
Any help appreciated.
Nelson
Nelson,
Add a title directly above the list
Select the title and list
Go to Data | Pivot Table
On the layout view, drag the title to the row field.
AND drag the title to the data field.
"OK" and "Finish" your way out of the pivot Table.
You will have a list of all the numbers with the count for each.
This can be copied and pasted where you want.
Jim Cone
San Francisco, USA
"Nelson" wrote in message
> Hi,
> I have a large list - and was wondering how to count unique numbers (eg.
> customer IDs) in a pivot table.
> I have arranged my data so each location is shown in the rows.
> Can this be done? So far I can only get the data field to count the total
> entries, but not unique entries by location.
> If not - is there a way to do this with a formula?
> Any help appreciated.
> Nelson
A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivot table.
For example, to count unique items in column A, use the following formula:
=IF(COUNTIF($A$1:A2,A2)=1,1,0)
Copy this formula down to all rows in the list, then add the field to
the pivot table.
Nelson wrote:
> Hi,
>
> I have a large list - and was wondering how to count unique numbers (eg.
> customer IDs) in a pivot table.
> I have arranged my data so each location is shown in the rows.
>
> Can this be done? So far I can only get the data field to count the total
> entries, but not unique entries by location.
>
> If not - is there a way to do this with a formula?
>
> Any help appreciated.
>
> Nelson
>
>
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
Thanks Debra, but that formula won't work effectively - I have about 38,000
rows of data to work with - which slows my xls down.
Nelson
"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:4257DA4E.8010200@contexturesXSPAM.com...
>A pivot table won't calculate a unique count. You could add a column to the
>list, then add that field to the pivot table.
>
> For example, to count unique items in column A, use the following formula:
>
> =IF(COUNTIF($A$1:A2,A2)=1,1,0)
>
> Copy this formula down to all rows in the list, then add the field to
> the pivot table.
>
>
> Nelson wrote:
>> Hi,
>>
>> I have a large list - and was wondering how to count unique numbers (eg.
>> customer IDs) in a pivot table.
>> I have arranged my data so each location is shown in the rows.
>>
>> Can this be done? So far I can only get the data field to count the total
>> entries, but not unique entries by location.
>>
>> If not - is there a way to do this with a formula?
>>
>> Any help appreciated.
>>
>> Nelson
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
The only other option that I can think of is to select the column with the ID
numbers, then use Filter/Advanced Filter, check Copy to Another Location and
Unique Records only. Then you can count the number of items in the new list.
On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
<bnilicSPAM-Avoidance-MECHANISM@ADDITIONAL-SECURITYgmail.com> wrote:
>Thanks Debra, but that formula won't work effectively - I have about 38,000
>rows of data to work with - which slows my xls down.
>
>Nelson
>
>
>"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
>news:4257DA4E.8010200@contexturesXSPAM.com...
>>A pivot table won't calculate a unique count. You could add a column to the
>>list, then add that field to the pivot table.
>>
>> For example, to count unique items in column A, use the following formula:
>>
>> =IF(COUNTIF($A$1:A2,A2)=1,1,0)
>>
>> Copy this formula down to all rows in the list, then add the field to
>> the pivot table.
>>
>>
>> Nelson wrote:
>>> Hi,
>>>
>>> I have a large list - and was wondering how to count unique numbers (eg.
>>> customer IDs) in a pivot table.
>>> I have arranged my data so each location is shown in the rows.
>>>
>>> Can this be done? So far I can only get the data field to count the total
>>> entries, but not unique entries by location.
>>>
>>> If not - is there a way to do this with a formula?
>>>
>>> Any help appreciated.
>>>
>>> Nelson
>>
>>
>> --
>> Debra Dalgleish
>> Excel FAQ, Tips & Book List
>> http://www.contextures.com/tiptech.html
>>
>
Ok thanks Myrna - I'll give that a go.
cheers
Nelson
"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:693h51lgrpmnqntma0co1ndk2np3ne5ra0@4ax.com...
> The only other option that I can think of is to select the column with the
> ID
> numbers, then use Filter/Advanced Filter, check Copy to Another Location
> and
> Unique Records only. Then you can count the number of items in the new
> list.
>
> On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
> <bnilicSPAM-Avoidance-MECHANISM@ADDITIONAL-SECURITYgmail.com> wrote:
>
>>Thanks Debra, but that formula won't work effectively - I have about
>>38,000
>>rows of data to work with - which slows my xls down.
>>
>>Nelson
>>
>>
>>"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
>>news:4257DA4E.8010200@contexturesXSPAM.com...
>>>A pivot table won't calculate a unique count. You could add a column to
>>>the
>>>list, then add that field to the pivot table.
>>>
>>> For example, to count unique items in column A, use the following
>>> formula:
>>>
>>> =IF(COUNTIF($A$1:A2,A2)=1,1,0)
>>>
>>> Copy this formula down to all rows in the list, then add the field to
>>> the pivot table.
>>>
>>>
>>> Nelson wrote:
>>>> Hi,
>>>>
>>>> I have a large list - and was wondering how to count unique numbers
>>>> (eg.
>>>> customer IDs) in a pivot table.
>>>> I have arranged my data so each location is shown in the rows.
>>>>
>>>> Can this be done? So far I can only get the data field to count the
>>>> total
>>>> entries, but not unique entries by location.
>>>>
>>>> If not - is there a way to do this with a formula?
>>>>
>>>> Any help appreciated.
>>>>
>>>> Nelson
>>>
>>>
>>> --
>>> Debra Dalgleish
>>> Excel FAQ, Tips & Book List
>>> http://www.contextures.com/tiptech.html
>>>
>>
>
Why not just use Debra's suggestion and then copy and paste special the
column as values. That way you'll have the data and see no impact on
performance. Obviously if you change the source data then you need to
repeat the exercise, but any other manual option will likely be more tedious
anyway.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Nelson" <bnilicSPAM-Avoidance-MECHANISM@ADDITIONAL-SECURITYgmail.com> wrote
in message news:eVkf5XXPFHA.2748@TK2MSFTNGP09.phx.gbl...
> Ok thanks Myrna - I'll give that a go.
>
> cheers
>
> Nelson
>
> "Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
> news:693h51lgrpmnqntma0co1ndk2np3ne5ra0@4ax.com...
> > The only other option that I can think of is to select the column with
the
> > ID
> > numbers, then use Filter/Advanced Filter, check Copy to Another Location
> > and
> > Unique Records only. Then you can count the number of items in the new
> > list.
> >
> > On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
> > <bnilicSPAM-Avoidance-MECHANISM@ADDITIONAL-SECURITYgmail.com> wrote:
> >
> >>Thanks Debra, but that formula won't work effectively - I have about
> >>38,000
> >>rows of data to work with - which slows my xls down.
> >>
> >>Nelson
> >>
> >>
> >>"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> >>news:4257DA4E.8010200@contexturesXSPAM.com...
> >>>A pivot table won't calculate a unique count. You could add a column to
> >>>the
> >>>list, then add that field to the pivot table.
> >>>
> >>> For example, to count unique items in column A, use the following
> >>> formula:
> >>>
> >>> =IF(COUNTIF($A$1:A2,A2)=1,1,0)
> >>>
> >>> Copy this formula down to all rows in the list, then add the field to
> >>> the pivot table.
> >>>
> >>>
> >>> Nelson wrote:
> >>>> Hi,
> >>>>
> >>>> I have a large list - and was wondering how to count unique numbers
> >>>> (eg.
> >>>> customer IDs) in a pivot table.
> >>>> I have arranged my data so each location is shown in the rows.
> >>>>
> >>>> Can this be done? So far I can only get the data field to count the
> >>>> total
> >>>> entries, but not unique entries by location.
> >>>>
> >>>> If not - is there a way to do this with a formula?
> >>>>
> >>>> Any help appreciated.
> >>>>
> >>>> Nelson
> >>>
> >>>
> >>> --
> >>> Debra Dalgleish
> >>> Excel FAQ, Tips & Book List
> >>> http://www.contextures.com/tiptech.html
> >>>
> >>
> >
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks