Pivot Table / Chart not grouping same entries

**bigpee** · 02-04-2010, 07:20 AM

Hi,

I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:

When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?

Sadly I am not able to post this spreadsheet but if anything needs clarifing please let me know.

Many thanks,

Alan

**Andy Pope** · 02-04-2010, 08:15 AM

If the items are not grouped together it usually means excel does not see them as the same, even if you do.
Normally caused by extra spaces.

Can you post example of records you think should be the same?

**bigpee** · 02-04-2010, 08:22 AM

Thanks for the reply - I initially thought it might be down to a space but I checked that. Attached is the list of routes minus all other data. This data is entered via a user form and is then analysed via a pivot table. Because the full database has sensitive information embedded in it this is probably the most information I can post which I appreciate is not the most helpful. The routes with a 'o' infront are like this to differentiate them from other busier routes.

**Andy Pope** · 02-04-2010, 08:35 AM

even with the general number format some of those cells are being treated as text.

I used Advanced autofilter to get a unique set of data, which I sorted.

I used some formula to illustrate what excel sees the cells as.

Finally to fix your data use the copy a 1, paste special/multiply trick to force numbers to numbers.

**bigpee** · 02-05-2010, 10:52 AM

Thanks for the help pointers - sorted the problem by doing the following:

Paste special (multiply) over the effected column. Then paste special (divide) to return to original values (but all with same formatting).

Next ensured that the column format was set to 'General' instead of text. This has solved the problem for future entries. Many thanks for your help.

Alan