Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VB
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel VB
Resolved · Urgent Priority · Version 2003
Reply Fri 24 Oct 2008, 12:12Delegate David said...
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?
For upcoming training course dates see: Pricing & availability
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
Reply Fri 24 Oct 2008, 14:58Delegate David said...
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
Attached files...
adv filter and pivot.xls
|