How do I append associated 'TEXT' field to 'Values' determination i.e. Max, Count, Sum in a pivot table?

[wesrockin]

I have a large database of census blocks with unique identifiers. There are multiple records per census block and each record has 0 -20 farm records with varying acreage (rounded to the nearest 10-acres (so this wouldn't necessarily create an opportunity to create a new identifier), and many other attributes of interest. I am summarizing the table to find the largest farm in each census block. Using a pivot table, I can pivot the unique census ids by the Max 'Values' of the acreages. NOW the crux of the issue is; How do I append the other attributes? Or How do I determine e.g. the name of the land owner associated with the largest sized farm.

This seems like it should be fairly easy, like just putting the text field in 'Values' in the pivot table, but that only handles numeric fields.


Thanks for looking

 

[Jerry Sullivan]

Hi wesrockin,

Yes, the cells in the Values area of the Pivot can only hold numeric or error values. There are some workarounds to display text, but those wouldn't be applicable for what you want to do.

Two approaches to consider...
1. Use an SQL query to return data you want from the records that have the greatest acreage for each block. The query results could be presented in either a PivotTable or an Excel Table (ListObject).

The Excel Table is more flexible for layout and formatting and I'd suggest that unless you have a need to use other features of a PivotTable.

2. Use a PivotTable with "Top 10" filters. Start with a Tabular layout that has all your attributes as Row Labels and the Acreage field in the Values area. Acreage can be summarized by either "Sum" or "Max" depending on how you want any Sub Totals or Grand Totals summarized. If you have a field that can serve as a unique identifier within any single census block, select on a cell in that field then:

Right-Click > Filter > Top 10...

In the dialog box use Top 1 instead 10 to display only the record with the greatest acreage. If you don't have a field that can serve as a unique identifier within any single census block, then you could repeat the process for more than one field until the combination of fields represents a unique identifier for each record. Note that if the greatest acreage value within a block is shared by more than one record then more than one records will be displayed as the "Top 1" for that block.