David has attended:
Access Advanced course
Excel VB
I have a lot of data listed in columns. Two of these colums are 'Area' and 'Case Number'. I have created a pivot table to summarise the data whereby I now have a count of the number of case numbers broken down by area. However the count is just counting the number of rows so if the same case number is listed twice it is recorded as 2 rather than 1.
Is there anyway of counting only unique case numbers?
RE: Excel VB
Hi David
Thank you for your question.
You can do this without using VBA.
Rather than creating the pivot table from your original data, first create a data list containing only unique records, then create the pivot table from the unique records.
To create a list of unique records, select the data range that you have currently. Then go to Data - Filter - Advanced filter.
Select Copy to another location under Action, then in the Copy to box, select a blank cell that will be the first cell in your list of unique records when it appears.
Tick the Unique records only box and click OK.
Then create your pivot table from the list of unique records.
I hope this helps - I don't know any VBA and we may not be able to provide a timely response to your question on how to resolve this using VBA at the moment.
Amanda
RE: Excel VB
Thanks for the quick reply.
Sorry i think i havent really explained this very well. Basically the advanced filter will pick up the unique case numbers but it losses the area from where they are from.
For example:
Area Case Number
Gloucestershire 1234
Gloucestershire 1234
Gloucestershire 345
Dorset 678
When I use the advanced filter it will show the unique case numbers as 1234, 345 & 678 but when I put it into a pivot table it will show the count of 3 against Gloucestershire rather than what I want it to show which is:
Gloucestershire 2
Dorset 1
Regards
Dave
RE: Excel VB
Hi David
I have used the data you provided above and created the pivot table from the filtered data (attached) - it seems to give me the result you are after. Did you create the pivot table from the results of the filter, or from the original data list?
Amanda
adv filter and pivot.xls
