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

wesrockin

New Member
Joined
Feb 21, 2004
Messages
18
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top