Pivot Table Text Grouping by the First 3 Characters

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

tcardwell

Board Regular
Joined
Dec 22, 2013
Messages
86
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

Board Regular
Joined
Jan 11, 2013
Messages
73
But how to use this feature here exactly? And it helps with the grouping purpose?
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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

screenshot-52.png
 
Last edited:

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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?
 
Last edited:

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
this is 2016 and as you can see DataModel exist

screenshot-53.png


now imagine the result from you example without DataModel :LOL:

raw dataPivotTable
threeonenumberthreeoneSum of number
AGUA
10​
AGUA
10​
AGUT
25​
T
25​
AGUX
57​
X
57​
ALXA
10​
ALXA
10​
HLPC
29​
HLPA
5​
HLPA
5​
C
29​

pivottablefields.jpg
 

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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.
 

Forum statistics

Threads
1,141,619
Messages
5,707,445
Members
421,508
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top