Collapse pivot table by number before the decimal point

**tomca** · 09-04-2015, 06:48 AM

Hi everyone,

Is it possible to collapse all the figures as shown in the image below?

I.e. the value of all figures under the A column with the integer "1" would be 2780.

Any would be great.

Thanks

Tom

grF8QP.png

**Melvinrobb** · 09-04-2015, 09:07 AM

The best thing to do is
1. in your original data source, add a column called "Grouping" and enter this formula (assuming column A is where your Item column is):

Formula:

Please Login or Register  to view this content.

2. Edit the pivot table data source to include this new column.
3. Drag the Grouping column into the AXIS (Categories) section.

**tomca** · 09-04-2015, 09:22 AM

That has worked, per the below image.

Thanks Melvinrobb

The next and final question is as follows:

I have some text items written in the original cells. E.g. A101 in the source data has the word "included" as opposed to a number value.

Is it possible to have this text still show up in A101 in the pivot?

ycXrPH.png

**Melvinrobb** · 09-04-2015, 02:34 PM

I am not sure what you mean.

If... in your original data source, in the 1.18 row, the notes column says "Included" you would want your pivot table to show as "1.18 - Included"?

**tomca** · 09-07-2015, 05:16 AM

Hi Melvinrobb, I've attached an Excel which I hope illustrates what I'm interested in. I'd like the text in the red cell of the Pivottable source to show up in the pivot table.

Thanks

Tom