Pivot table not recognizing same field values

[neilmber]

I created a pivot table in Excel 2003 that uses five field list on a sheet in
the same workbook. The fields are Promotion, Paper, Amt, Month, and
PromoCategory. The pivot table is returning multiple records for what appear
to be identical values in the PromoCategory field.

I consolidated data from January through March from a 70-column wide sheet
that staff had been using. For that period, and one record in the pivot is
returned for the January through March data and another for the newly entered
April data. The early data has no value in the promotion field (which is not
used in the pivot). The April data--but not the early data--uses a vLookup
function to populate the PromoCategory field from the Promotion value.

I can group the pivot table records where the PromoCategory produces two
rows, but it is time consuming and the enduser is not ready to do this each
month. And even when grouped, the final report, which uses GetPivotData,
can't seem to find the newly grouped data.

 

[Debra Dalgleish]

As answered in microsoft.public.excel.misc:

Even though the PromoCategory items look the same, some probably have
one or more space characters at the end, and that makes them different
in the pivot table.

Perhaps you can change the source data, or use a TRIM function to remove
any trailing space characters.