Why do identical values show up twice in Pivot Table?

**desperatepivot** · 07-11-2011, 06:47 PM

Hi everyone:
I am working on a pivot table that includes 71 different cities and the factors assigned to each of them. When I organize the data by factors, some of them (not all) appear twice, even though they are idential, have same formatting, etc.

For instance, there are seven cities with a factor of 1, and instead of the pivot table summarizing all of these 7 cities under the factor 1, it first displays Cities 13, 22, 48 and 52 under the factor of 1, and then summarizes cities 49, 51 and 54 under (again) a factor of 1!!

Can someone kindly fix this? (I've attached the file for ease of reference)

DesperatePivot

**TMS** · 07-11-2011, 07:09 PM

It would help to see a sample workbook with some typical data. Although you said you'd attached a file, you didn't. ;-)

However, in the absence of anything to look at, I would guess that some of the factors are true numeric data and some are text values that look like numeric data.

Left aligned "numbers" will be text; right aligned number will be numbers ... unless you have formatted the data to align it right or left. If that's the case, you might have to remove any justification.

It could be dependent on the source of the data.

Regards

**desperatepivot** · 07-11-2011, 10:07 PM

Hi, many thanks for the prompt reply. Here is the excel file attached with the pivot table.

I checked the data and they all are numbers.

Desperate Pivot

**Paul** · 07-11-2011, 10:26 PM

Hi Desperate,

There is something wrong with the data in the factor values (b6:b76). If you select those cells, then click Data > Text-to-columns > Finish, and then refresh your pivot table the numbers group properly.

**desperatepivot** · 07-11-2011, 11:19 PM

Paul, many thanks, that did it!!

(It surprises me that the data were text instead of numbers. The data come from a formula that is calculated in a separate worksheet, and the formula is the result of multiplying two vectors, which to me means that the data ought to be numbers (and not text)).

A happy pivot,

**TMS** · 07-12-2011, 05:58 AM

Odd, they do look like numbers and they're formatted as numbers.

You can also press F2 on individual cells to force Excel to "take a second look" but it's not obvious why it's seeing them as different.

If you compare "like" factors in different groups, they appear to be the same:

=B18=B54 => TRUE

Glad you have your answer though. Thanks for the rep.

Regards