Pivot Table Repeats Names in multiple rows
 

I have data on hours worked for about 70 employees (Names are rows) for the
last year (Months are columns). For about 15 of those names, the Pivot Table
is not consolidating all the data for that employee. Rather, it repeats the
name for 2 or more rows as if those names were spelled in various ways. The
hours data for those affected employees are also broken out among the various
rows. The source data originally had this problem, but I fixed all the
misspellings. By filtering the names field in my source data, I have
confirmed that there is now only one version of each name. Why can't the
Pivot Table recognize this? I've already tried refreshing the data and
restarting the computer. Any suggestions?

Try looking for extra spaces.

Autofilter ignores the trailing spaces. Pivottables don't.

You could even use something like this when you're looking at the pivottable:

=len(a5)
=len(a6)
(in a couple of unused helper cells--if A5 and A6 look identical to the naked
eye.)

Riccardo wrote:

I have data on hours worked for about 70 employees (Names are rows) for the
last year (Months are columns). For about 15 of those names, the Pivot Table
is not consolidating all the data for that employee. Rather, it repeats the
name for 2 or more rows as if those names were spelled in various ways. The
hours data for those affected employees are also broken out among the various
rows. The source data originally had this problem, but I fixed all the
misspellings. By filtering the names field in my source data, I have
confirmed that there is now only one version of each name. Why can't the
Pivot Table recognize this? I've already tried refreshing the data and
restarting the computer. Any suggestions?

--

Dave Peterson

Thanks Dave! That was exactly the answer I needed!

"Dave Peterson" wrote:

Try looking for extra spaces.

Autofilter ignores the trailing spaces. Pivottables don't.

You could even use something like this when you're looking at the pivottable:

=len(a5)
=len(a6)
(in a couple of unused helper cells--if A5 and A6 look identical to the naked
eye.)

Riccardo wrote:

I have data on hours worked for about 70 employees (Names are rows) for the
last year (Months are columns). For about 15 of those names, the Pivot Table
is not consolidating all the data for that employee. Rather, it repeats the
name for 2 or more rows as if those names were spelled in various ways. The
hours data for those affected employees are also broken out among the various
rows. The source data originally had this problem, but I fixed all the
misspellings. By filtering the names field in my source data, I have
confirmed that there is now only one version of each name. Why can't the
Pivot Table recognize this? I've already tried refreshing the data and
restarting the computer. Any suggestions?

--

Dave Peterson

Pivot Table Repeats Names in multiple rows
 

I've actually found that pivottable is adding a leading and a trailing space
to my row header. I think there's a bug in this beta.

My model gets all #refs when I try to do a GetPivotData call and my search
term has no spaces but the pivottable itself has spaces added.

Weird.

Anyone else seen this?

"Riccardo" wrote:

Thanks Dave! That was exactly the answer I needed!

"Dave Peterson" wrote:

Try looking for extra spaces.

Autofilter ignores the trailing spaces. Pivottables don't.

You could even use something like this when you're looking at the pivottable:

=len(a5)
=len(a6)
(in a couple of unused helper cells--if A5 and A6 look identical to the naked
eye.)

Riccardo wrote:

I have data on hours worked for about 70 employees (Names are rows) for the
last year (Months are columns). For about 15 of those names, the Pivot Table
is not consolidating all the data for that employee. Rather, it repeats the
name for 2 or more rows as if those names were spelled in various ways. The
hours data for those affected employees are also broken out among the various
rows. The source data originally had this problem, but I fixed all the
misspellings. By filtering the names field in my source data, I have
confirmed that there is now only one version of each name. Why can't the
Pivot Table recognize this? I've already tried refreshing the data and
restarting the computer. Any suggestions?

--

Dave Peterson