Odtnhj

I have some data that I need to find an average day rate for a certain company. The data consists of 1000's of names, some duplicates with a day rate against them.

If I insert a PivotTable and filter on the company, the Average day rate will show, however, I know it is wrong as it is including the duplicate names.

I have got round this by double clicking on the average value and bringing up the data for that company in a new tab. Then I can insert another pivot table to remove the duplicates and then from that, find the average day rate.

Is there a way to by pass having to insert another pivot table, on the filtered tab?

FYI I'm very new to pivot tables.

You can make a "count of Names" values field and filter the names from the "Row Label" after you have used your report filter. You just have to filter by company, then click on Row Labels => Value filters => Select which column value you want to filter (count) => Select "Greater Than 1".

One additional note. If you make your data into a Table then there is an option to remove duplicate entries once you are done finding the average. It will definitely be faster than doing it by hand, however it removes the last duplicate entry so be careful

An alternative, if sorting is allowed, might be to restrict your single PivotTable to data that is not duplicated. This might be achieved with a formula such as:

 =COUNTIF(A$1:A2,A2) 

in Row2 and copied down to suit, where I assume ColumnA contains names and you only want one instance and any one instance of each name. Sort on ColumnC and restrict the range for your PivotTable to those rows where the formula returns 1.

A possible drawback however might be if you want certain individuals included more than once based on other criteria. For example if Mrs Roberts is in the list as both Clerical and Management you might want her to be treated as a separate individual for each role.