Pivot Table Text Grouping by the First 3 Characters

[Magdoulin]

Hi guys, I need a help with the Pivot Table please, I need to group text fields automatically by the first 3 characters, how could I do that? Noting that I need to do this in the same workbook because I came across some solution but it needed to create new workbook for the Pivot Table. I do not want to proceed with manipulating the raw data as well, I need to do this through the Pivot Table itself please.

 

[tcardwell]

Sounds like a perfect use for the Calculated Field feature in a pivot table. Formula for the field would be something like LEFT(Customer,3).

 

[Magdoulin]

But how to use this feature here exactly? And it helps with the grouping purpose?

 

[Magdoulin]

Ok, anyone got any clue how we could get this sorted out guys please?

 

[sandy666]

add table to DataModel
create new column, eg. =left([Surname],3)
create PivotTable from there
put the new column (with three letters) as first into the ROWS area
the rest is up2you

example

 

[Magdoulin]

But I don't want to added any extra column to the raw data for some project consideration
I sense that there is another way to do it through the Pivot Table itself.

 

[sandy666]

I didn't add any column to the raw data, I added column in DataModel
re-read my post

D'ya have a PowerPivot? or better: what is your Excel version?

 

[Magdoulin]

I don't get it, what do you mean by DataModel
I use Office 2016

Imagine this as a raw data for instance:
AGU A 10
AGU T 25
AGU X 57
ALX A 10
HLP C 29
HLP A 5

 

[sandy666]

this is 2016 and as you can see DataModel exist

now imagine the result from you example without DataModel

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]raw data[/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]PivotTable[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]three[/td][td]one[/td][td]number[/td][td][/td][td=bgcolor:#DDEBF7]three[/td][td=bgcolor:#DDEBF7]one[/td][td=bgcolor:#DDEBF7]Sum of number[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AGU[/td][td]A[/td][td]

10​

[/td][td][/td][td]AGU[/td][td]A[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AGU[/td][td]T[/td][td]

25​

[/td][td][/td][td][/td][td]T[/td][td]

25​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AGU[/td][td]X[/td][td]

57​

[/td][td][/td][td][/td][td]X[/td][td]

57​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ALX[/td][td]A[/td][td]

10​

[/td][td][/td][td]ALX[/td][td]A[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]HLP[/td][td]C[/td][td]

29​

[/td][td][/td][td]HLP[/td][td]A[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]HLP[/td][td]A[/td][td]

5​

[/td][td][/td][td][/td][td]C[/td][td]

29​

[/td][/tr]
[/table]

 

[Magdoulin]

I guess, I managed with the DataModel tool.
I did not see that it is an Add-in before.
And it's my first time to hear about this tool.
But it's cool, it seems to me that it's got more power than that.
Gotta to check it out more later.

Thank you! You were very helpful.